THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Leaking roof and file shrinking

Shrinking and growing database files cost you resources and have other side effects as well. I have a technical description  http://www.karaszi.com/SQLServer/info_dont_shrink.asp if you want to get into details about it. Sure, you can have valid reasons to shrink, after archiving lots of data and needing to free some disk space for example. But even after pointing to my article I see people doing this at a regular basis, hence this analogy. The analogy uses transaction log file as example, but the same basic principles applies to data files:

Say you have a leaking roof at the office. You need to put a bucket for the water under the leak (the bucket is your database file, a log file, .ldf, for this analogy).

Ever so often, you need to empty the bucket (backup log if in full or bulk logged recovery model, or when checkpoint occurs if you are in simple recovery model). Emptying the bucket doesn't make the bucket smaller. The bucket still consumes the same amount of cubic cm of your office space.

You can of course have one of the office workers (the machine resources) take an angle grinder and make the bucker physically smaller when you empty it. This consumes resources. But you would now have an office worker to use a welder, perhaps every 10 minutes or every hour to make the bucket larger, as you have more and more water in the bucket.

I don't know anyone who had a leaking roof, but if I were in that situation, I sure wouldn't make this bucket larger and smaller all the time. I'd use a bucket with a size necessary to accommodate the water that builds up between when I empty the bucket. This is how I handle my database files as well.

Published Sunday, February 25, 2007 11:31 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Tibor.

This is great metaphorical example. I'm sutre it will make people to review their  vison about shrinking files

March 1, 2007 3:36 AM
 

asim kurcevic said:

Ti si tito Tibor!

May 28, 2009 6:24 AM
 

Aaron Bertrand said:

I think that people have been lulled into the false sense of security that you can set up a SQL Server

June 21, 2009 1:43 PM
 

Jonny Gray said:

Honestly, the analogy does make sense to a degree, however, it is somewhat ridiculous at the same time.  It only makes sense if the target reader has no idea what they are doing, and thus, has not set up a scheduled job to daily dump the temp data from the LDF to a TRN file then shrink the LDF... this is the way SQL databases are designed to be managed.

By design the LDF file is simply a temp file where new data is stored until it can be checked into the MDF (which is usually set up to happen fairly immediately), but the data cannot be dumped from the LDF until it has been backed up (either to a TRN, or via the "truncate_only" backup), but the LDF file MUST be shrunk or it will continue to grow until it fills the hard drive.  Again, it is by far the best practice to set up a scheduled job to back up the MDF & LDF once or twice per day so that the LDF will be shrunk regularly and not reach an unmanageable size.

Servers are constantly writing to and deleting from their hard drives... that is what they do!  True, shrinking an LDF file will use system resources momentarily, but it is not as if those resources are not immediately freed up afterward.

June 23, 2010 10:28 AM
 

TiborKaraszi said:

Jonny,

It seems you are confusing removing information from the file (aka truncate, or as I like to call it emptying) - which in fact is just marking the VLF's as overwritable - and making the file smaller.

The normal way to handle the ldf file is to regurarly backup the log. SQL Server will now be able to re-use the space in the file. No need to shrink the file.

I.e., your statement "but the LDF file MUST be shrunk or it will continue to grow until it fills the hard drive" is incorrect. I manage many many databases where I *never* shrink the ldf file and the storage within the file gets re-used over and over again.

That is the heart of the analogy. More details in the article I referred to.

June 23, 2010 2:44 PM
 

Tim said:

This is great! I'll use this analogy next time I'm trying to explain to the sysadmin why I'm not shrinking the log files.

Thanks,

Tim

July 6, 2010 11:44 AM
 

TiborKaraszi said:

Thanks Tim. I don't know why it is so hard to get this message through, but I guess it is just one of those things you need to keep banging... :-)

July 6, 2010 12:03 PM
 

fawwad said:

Just awesome :)

i love the way you explain it.

August 9, 2010 7:51 AM
 

Analogy Works said:

But tell me someone is going to fix the roof :)

Dex

February 19, 2011 9:51 AM
 

azar said:

Hello Tibor,

I have an SQL database for software that does not support restoring transactions from the log file.  If anything goes wrong, we would just restore a full backup.  So, if the software doesn't need it or use it, is there any harm, performance wise, in me shrinking the log file and changing the recovery mode to simple?

Thanks,

May 1, 2011 3:47 AM
 

TekGems said:

My issue is that I am using SQL Server Express 2005 and the maximum database file size is 4GB. We sometimes hit this ceiling. Since there is a hard file size limit, we use Auto Shrink to minimize reaching this threshold. I'm not sure how to handle database fragmentation within the file (like you can with MS Access Compact feature), but we handle fragmentation on the file level by running contig.exe every night.

February 22, 2012 9:23 PM
 

TiborKaraszi said:

The thing with analogies is that they are only ... analogies. Pushing them too far and the analogy break down!

Fact is that SQL Server generates log records to support things like crash recovery, transactions, etc. We can determine when VLFs in the ldf files are re-used by recovery model, and some operations can go minimally logged (also determined by recovery model).

June 19, 2012 4:34 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement