One of my databases had 450 GB of size for the data mdf file. I Dropped the unecassary tables to reclaim some space in the database and to my surprise even if there was around 150 GB of freespace, the data file won't shrink below the Files initial size which has grown to 450 GB. What could be the simplest solution/ workaround for this ?
A:
If your database is in FULL Recovery mode you can switch to SIMPLE mode then do a shrink files to reclaim space.
Normally it's due to the logs not being able to successfully shrink rather than data file.
However ensure you leave enough space for growth as the resizing will cause a lot of extra work.
I wouldn't recommend shrinking for no reason as there are plenty of posts out there indicating why you shouldn't as it can lead to fragmentation.
Check books online for the DBCC SHRINKDATABASE statement as well.
Justin King
2010-08-05 22:44:42
Choosing the closest although its not the complete answer
Baaju
2010-09-08 08:48:57