I have a database file that's currently 150GB, but only 75GB is being used – it's because I moved all the indexes (the other 75GB) to a new data file. I'd like to reclaim at least part of the space from this data file, but when I attempt to shrink the file, it "Executes" indefinitely, eventually being cancelled because of a network interruption or something else out of my control (after a day of running). Even using the "shrink to specific size" feature and specifying that it just trim off 10MB never seems to return – it just sits until the process is interrupted.
Is there another way that I can reclaim this space, even a little at a time?
EDIT: Somebody posted a link explaining why I shouldn't shrink my database. I understand, and I want to shrink it anyways. Disk space is at a premium on this server, and the database will not expand again into this unused space for a very long time – as I stated earlier, I migrated indexes out of the data file to free up this space, so now it's wasted.
Best Answer
No, using
DBCC SHRINKFILE ('filename', target_size)
is the right way to do it.If you want to do it in "chunks", you can either set progressively smaller target sizes, or just let it run as long as you can before it gets cancelled.
A few comments:
DBCC SHRINKFILE ('filename', TRUNCATEONLY)
. It should recover all the space its already freed at the end of the file (see my prior point)