May 20, 2010 at 8:50 am
I have a database structure in SQL 2005 that I inherited from a previous application.
Rewriting the application using the same database resulted in a number of large tables no longer being required.
These tables were dropped from the database.
There was about 5GB of data in the tables that I dropped so I assumed the size of the .mdf file in the 'Data' directory would be reduced.
It was 14GB before the drop and it was 14GB after the drop.
Therefore, I assume that the .mdf is still holding onto the data somehow.
Is there a function or procedure that I can run that will cleanup the database and,thus, reduce the size of the .mdf?
Leonard
May 20, 2010 at 9:19 am
You need to shrink the database when there is any large data trunacations / deletes.
Use DBCC ShrinkDatabase command to do this..
You can do it from SSMS also..
Read more details in Books Online here http://msdn.microsoft.com/en-us/library/ms190488(SQL.90).aspx
Keep in mind, you need to shrink only if you ar sure that the database will not grow again.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 20, 2010 at 9:32 am
Leonard 40050 (5/20/2010)
There was about 5GB of data in the tables that I dropped so I assumed the size of the .mdf file in the 'Data' directory would be reduced.It was 14GB before the drop and it was 14GB after the drop.
Therefore, I assume that the .mdf is still holding onto the data somehow.
Leonard
the .mdf is holding the empty space, and it will be reused as needed. If you can afford to keep the space allocated, then just leave the database alone. Then you won't cause fragmentation issues from shrinking.
May 20, 2010 at 9:35 am
I'm with homebrew01. The space will get reused. It isn't in the backups (that size should have dropped), but growing a db is an expensive operation. You don't want to do it often, and you want space.
If you shrink, you can fragment tables and indexes, so after you shrink, you need to have enough space to rebuild indexes.
Leave if if you can. 5GB isn't a lot of space, and it will get used over time. If it's not going to be used in a year (check backup size growth), then you might shrink out some of it to make restores/dr easier.
May 20, 2010 at 9:50 am
Thanks to all who replied.
I have my answer.
It really was the backups that I was concerned about.
Since the Backup will be smaller I am OK with the size of the .mdf remaining as is.
Your help is appreciated.
Leonard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply