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
DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)
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
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