Shrinking is a popular topic…

This is part 2 in a series on Shrinking “Best Practices” and logging. Click here for part 3. You can see them all in the Shrinking & Transactions category.

A while back I posted about not touching the shrink button. I am still finding it fun to see what search queries bring traffic to my blog so I look at the search query log every once in awhile. That shrink post is coming up the most in searches which leads me to believe I should add a few more points.

As I said in that post there are some great comments on shrinking over at Paul Randal’s blog. I suggest you definitely take a visit there, in fact if you had to stop reading and go to his blog, I wouldn’t be offended in the least.

Let’s talk about some of the more common questions that I see or infer from the web searches…

My Transaction Log Has Grown Too Much or My Transaction Log is Out of Space

If you have come here because your transaction log is growing out of control there is a strong chance that you are in Full Recovery mode and are not backing up your log file on a regular basis. Your transaction log continues to grow until you explicitly backup that log (a full backup won’t do). This is the expected behavior since Full Recovery mode means you want the ability to backup to a point in time and as long as your backup is someplace safe minimize your losses to the frequency of your backups.

Solutions:

1.) Setup a log backup schedule that meets your business needs. Search books online and understand recovery models, figure out the SLAs you are supposed to be supporting (do it before it’s too late since you are here reading this and Mr. Murphy likes to attack in these kinds of situations). Once you figure that out get your logs backing up on that schedule. Make sure they are going to a drive other than your mdf/ldf files so they are useful in the event of a failure. Perhaps even to tape directly or after a copy. You should see your log files become more manageable size-wise, hopefully.

2.) Get more space. Maybe you are doing Log backups but you still don’t have enough space. Either your activity is quite high or your allocated space is quite low. If it’s the former maybe a more frequent log backup helps. If it’s the latter or former, more space for your log files may be required.

3.) Switch to Simple Recovery Mode. This is not to be done lightly. You are no longer able to restore to a point in time but can only restore to the last full backup. Maybe that is fine by your SLA. If it is and you have no desire to restore to a point in time, switch to simple. Your log file will now truncate (see below) on certain intervals.

4.) While you are adding that space or setting up your backup look at your growth ratio? Is it the default 10% for a transaction log? How large is your log file? Is 10% really the right amount you want to see it growing by? On that same note has your log file grown a lot larger than it need be because of poor management? Perhaps once you do your first T-Log backup, you should look at setting a reasonable size knowing that it will be truncated on a regular basis. If that is considerably smaller than where you are, maybe one last shrink of the log file is warranted. (Don’t shrink the database! Just the file. Here sis a good reference on DBCC SHRINKFILE so you can see how to just shrink that one file).

What is the Difference Between Truncate and Shrink?

A lot of people get confused. They have truncated their log file yet they still have no free space and the file hasn’t reduced it’s footprint at all. This is because a truncation does nothing to the physical size of the allocated file on the OS. A shrink operation shrinks space from a file and a truncate essentially frees up the used space within that file.

This is why a shrink on a log file that is using all of the space won’t budge the size and why a truncate of a log file won’t budge the size. A truncate would have to happen first to make room available for the shrink to work. I do NOT suggest this, however. Another great post about why not to srink your log file is done by Tibor Karaszi here.

Someone on the newsgroups told me to…

A large portion of the folks responding to questions in the newsgroups and forums are giving great advice. They are taking their own time to help guide you down the right path. That being said, there are at times pieces of bad advice that hopefully aren’t followed in production. I talked about this practice of doing before trying in an earlier post. Gail Shaw also recently posted about this same topic on her blog.

Judging by some of the searches I have seen, it looks like people are getting some bad advice but at least trying to verify it through google (I would hope the next step is understanding the pieces involved and a test in dev/test/sandbox). A couple examples of that advice and my responses:

Stop SQL, or deatch the DB and delete the log file – No. This will most assuredly remove any transactions in your log, can leave your DB in a transactionally inconsistent state meaning a potential for loss of data or worse. If you are stuck without space for further growths, try the log backup, if you must do one last truncate and shrink after making a full backup and heeding the warning to get a real recovery strategy in place.

Setup a nightly job to issue BACKUP LOG WITH TRUNCATE_ONLY – I hopefully don’t need to say much more about this one that I didn’t say here or in my previous shrink post.

If any of this isn’t clear or you are stuck with an uncomfortable situation, send me an e-mail or leave a comment, recovery is important and I hate to see people messing this up because of bad advice or misunderstanding. I won’t charge you for any time for quick help with something like this.

This should be my last post on shrinking 🙂