Database Cleanup

Question

Database Cleanup

jnodarse

SSC Enthusiast

Points: 152
More actions
December 11, 2008 at 1:19 pm

#126410

I have a database that is 22gigs. I cleaned up the database and removed allot of old and not needed data. I also do local Backups on the databases nightly. I now notice that the database .bak file is 8gigs and no longer 22gigs. But when I go to my SQL Data folder I see that the MDF file is still 22gigs. Is this because I need to run a shrink command on the database to allow SQL to see its current size?
If yes can someone please send me the syntax for the command I need to run and is it safe to run this shrink command whenever on the database or should I do it off hours. I will be sure to take a backup of it first of course.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

first of all only shrink the .mdf file if you are sure it won't need to grow to that size again.

If you do shrink it do it out of hours at a quite time. The process will also fragment the data in the database so follow the shrink by a reindex.

The shrink command is in Books online but it is:

dbcc shrinkfile(logicalnameofmdf,sizetoshrinktoinMB)

i.e. dbcc shrinkfile(database_data,10000)

leave about 10-20% freespace in the database, use that rule ro decide on size to shrink to.

---------------------------------------------------------------------

jnodarse SSC Enthusiast Points: 152 More actions · Answer 2

The database does have potential to grow back to that size so do you recommend for me to just leave it alone if this is the case?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

jnodarse (12/11/2008)
The database does have potential to grow back to that size so do you recommend for me to just leave it alone if this is the case?

yes. its an unnecessary use of resources to shrink and then have it grow again, particularly as it may grow during busy times.

---------------------------------------------------------------------

Joseph-465703 SSCrazy Points: 2122 More actions · Answer 4

can you execute sp_spaceused ( please refer in Books OnLine) on the tables on which you deleted rows/data. event hough you delete rows, it wont update size of tables right away.