Shrink a database below its initial size I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% unused.

I need to reclaim the space, which will allow us to have a second dev DB on the server (based on the cut down version); however, I cannot reclaim the space, I have done the following:


The initial size of the file SMS2_Data is 30GB.

DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)


followed by 

DBCC SHRINKFILE (N'SMS2_Data' , 19500)



No joy. I have tried making a backup, creating a new DB with a low initial size then restoring, no joy as the initial size gets overwritten. Have also tried:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000) 


This errored, saying:


  MODIFY FILE failed. Specified size is less than current size.


I tried 20800 and then kept going up til 29000 (29GB) and it still will not let me change it.

Have done the shrink then changed the recovery mode from FULL to SIMPLE and back again. No joy.

I thought it was to do with some TEXT fields. We have about 6 across the system. So as a test I dropped them all and then did a shrink of the file and still no change.

The only option left is to reimport the data to another DB. This is not practical, as it would have to be done on the live DB, which carries too much risk. We semi-regularly grab a copy of the live DB and overwrite dev/test. We have something like 500 tables. I would like a way of doing it that would not have the risk of exporting data to a new DB.

I tried moving the data to another file, and it copied all but 5% of the data. This is what lead me to try and drop all the text columns.

The server is on compatibility mode 90, but is SP2. I've now done the following 3 times: reindex all tables, backup database, shrink file, shrink database. Still no joy.

EXECUTE sp_spaceused returns:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB
As some people already mentioned you could create new database and "copy" stuff over from old database. This would be the best option for you. However I've noticed that you want to do it quite regularly. So your best option is Redgate Data Compare and Redgate Compare. Both are part of Redgate SqlToolbelt package. 

So what you do:


Create an empty DB with small initial size.
Use Redgate Compare to copy db structure, functions etc from old db
Use Redgate Data Compare to copy data from old database to new one
You work on dev database and then at any time you either do just Data Compare and update the Dev DB regularly, or if you do any changes to db you can deploy those changes using Redgate Compare and then doing Redgate Data Compare.


What is good with Data Compare is that after you copy those 30gb of data (you can do it starting with some tables only) after a while it just needs to 'recompare' only some changes and not whole 30gb of data. Which means it will do a lot less impact on both databases then it would by copying it normally.