I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive.
Yesterday evening I dropped a large table that was no longer needed and last night I set up a maintenance plan to shrink the database with 25% free space to remain after shrink, and to rebuild the indexes.
The job ran successfully, however the size of the .mdf file was only reduced by about 1.5GB even though the sp_spaceused results indicate that most of the database is unallocated space.
database_name,database_size,unallocated space
redacted,28717.69 MB,28349.03 MB
I was expecting a far larger decrease in the size of the database.
Did I improperly configure the maintenance plan? Am I misunderstanding what happens when a database is shrunk? Both?
Best Answer
When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.