Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Database Shrinking

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-09 : 08:24:15
Stephanie writes "I have been searching all weekend for information about database shrinking. I know how to shrink databases, but I am not sure why shrinking is used. I have an idea but I thought it was too fundamental to be the only reason why we shrink databases. Is it only remove unnecessary space from database files?

Also, what is the best practice for the amount of unused space in a database? I mean, I can shrink a database so that there is 0% percent (the SQL default) of space left, but is that a healthy practice. The database is still set to grow automatically as necessary, but I was sure if shrinking down to 0% available space is necessary.

Thanks,
Stephanie Jones"

Kristen
Test

22859 Posts

Posted - 2006-01-09 : 12:42:46
Do you have a runaway Log File? (i.e. the LDF file is massively bigger than the MDF file?)

If so see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why+is+my+LDF+Log+File+so+big

Otherwise don't shrink it. If you have deleted an exceptional amount of data, or run some unusually large query etc. which has expanded your logs (LDF file(s)) hugely then MAYBE shrink it, but not otherwise.

Shrinking the database will just cause the files to need to grow again immediately as new transactions arrive; that in turn will take more CPU effort, reduce the availability of the system during the extension (which may lead to application errors & timeouts) and increase the fragmentation of the file.

Paul Randal (Lead Program Manager, Microsoft SQL Server Storage Engine) said in another thread:

"Don't shrink your database. Shrink causes index fragmentation - I've specifically documented this in the BOL for SQL Server 2005.

You only need to worry about fragmentation in indexes used for range scans - see our whitepaper below for more details.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
"

IME the Log File (LDF) will grow to 120% ~ 130% of the Data File (MDF) in "normal" use, so don't panic and shrink if its less than that.

If the filesize of the Log File is "stable" - not continuously growing, and didn't suddenly leap to a much bigger size (e.g. because of a one-off massive deletion) - then it most probably does not need shrinking.

We allocate a best-estimate initial size to the MDF and LDF, and then take the DB offline, and defrag the physical file, then put it back online. We do this again after any significant growth in the file (which would obviously also include shrinking it and it then growing again).

If you have massive deletes to do (e.g. stale transactions or logging data) then do them in manageable batches - e.g. delete 10,000 rows at a time, rather than 10,000,000 at a time! This will use less log space for each batch, tie up the database for shorter periods, and allow you to run [frequent] transaction backups to keep the LDF file size under control.

If you are worried about fragmentation of indexes consider REINDEX and DEFRAG (for some guidance on that see Tara's blog for ideas, and code!, for Admin procedures:

http://weblogs.sqlteam.com/tarad/category/95.aspx

"The database is still set to grow automatically as necessary"

Set it to grow by a number of MEGABYTES that you are comfortable with (and NOT a percentage) - when your database is several GB then extending it by several hundred megabytes each time (i.e. if you have expansion set to 10%) will kill performance - IME for up to 30 minutes!!

Kristen
Go to Top of Page
   

- Advertisement -