I've got a dev database that is a copy of live 30gb, we have deleted some data that is not needed in dev that brings the data file used to 20gb.
Its SQL server 2005
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)
DBCC SHRINKFILE (N'SMS2_Data' , 19500)
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
ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000)
errored saying MODIFY FILE failed. Specified size is less than current size.
Have done the shrink then changed the recovery mode from FUll to simple and back again. no joy
ADDITION: So I thought it was to do with some TEXT fields. We have about 6 accross the system. So as a test I dropped them all and then did a shrink of the file and still no change.
ADDITION 2 : Cheers everyone for your answers, no single answert has sorted teh problem. The only option left is to reimport the data to another DB. This is not practicle as it would have to be done on the Live DB, which carries too much risk. I've no idea what will happen to the bonus points. I cant pick an answer as nothing has given me a solution to my problem
ADDITION 3 : I've tried lots of things and nothign has worked, thanks all for your help but I've just got to accept that its gonna be 30 gig at least.