THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
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

Leave a Comment

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