How To Shrink a File to Below the Size value in SQL Server 2005
Posted on 2008-02-05
The database I am working on was created as a restore from a production backup.
The 'Initial Size' of the data file is automatically set to the actual size of the file (or close to it)
In my case, it is 100gb.
I have since added another File Group (and hence file) and moved over half of the tables to it.
I need to reclaim the free space in the original file.
The DBCC SHRINKFILE (N'myDatabase.Primary' , 70000) does not have any effect (presumably because it will not shrink to below the Initial Size)
When I try to reduce the original size using
ALTER DATABASE myDatabase MODIFY FILE (NAME = N'myDatabase.Primary', SIZE = 50gb)
I get the error 'MODIFY FILE failed. Specified size is less than current size.'
Has anyone got any ideas on how I can reclaim this free space when the Initial Size of the file is way too big
thanks in advance