SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Database Shrinking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/09/2006 :  08:24:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/09/2006 :  12:42:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000