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?

  • 1
    What is the recovery model of the database ? – Kin Shah Nov 21 '13 at 16:00
  • 1
    Have you checked the "related questions" to the right ---> – Max Vernon Nov 21 '13 at 16:56
  • The database is in full recovery model. – Seth Nov 22 '13 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 '13 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 '13 at 21:05
0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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