0

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?

5
  • 1
    What is the recovery model of the database ?
    – Kin Shah
    Nov 21, 2013 at 16:00
  • 1
    Have you checked the "related questions" to the right --->
    – Hannah Vernon
    Nov 21, 2013 at 16:56
  • The database is in full recovery model.
    – Seth
    Nov 22, 2013 at 14:38
  • Did the table that was dropped contain Large Binary Objects, such as text, image, etc? If so, that space is not readily recovered.
    – RLF
    Nov 29, 2013 at 15:37
  • One column was typed as text. Based on your comment, I'd guess that's the issue. Do you know of a resource I can check out for more information about this kind of space not being readily recovered?
    – Seth
    Dec 3, 2013 at 21:05

1 Answer 1

1

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.

1
  • Thanks, Troy. I'll use the ShrinkFile command in the future.
    – Seth
    Jan 2, 2014 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.