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.

Victim of your database shrink operation?
Save The Kittens! Stop DB Shrinking!

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 🙂

10 thoughts on “Shrinking is a popular topic…”

  1. Any reason why a log file continues to grow with the recovery model set to simple when the log_reuse_wait_desc shows "Nothing"? This is a perplexing problem and I would really rather not shrink the file. So far, that’s all I can do to maintain the file size.

    Reply
  2. Ed –

    Good question, the column value of "Nothing" for the log_reuse_wait_desc simply is telling you that there is nothing currently blocking the reuse of the log file. In other words the log has portions available for reuse at that time.

    This should mean that as your log file is needed, data can reuse portions that are available. My guess is that you are potentially seeing a lot of activity that is causing the growth to grow beyond the available virtual log files. Perhaps from a long running transaction that you are not seeing at that exact moment you are querying sys.databases.

    If you look at the free space of your log file over time, do you notice it ever has plenty of free space available? Remember, the log file will grow as long as needed for the transactions occurring between checkpoints. Once a checkpoint is able to occur, the log file will be truncated (used space will be freed) but it will not be shrunk (the physical file size lowered).

    Shrinking is a separate operation and if the file needed to grow to that size once it has as a strong potential of needing to grow to it again at some point so it is best to not shrink but just watch to make sure it doesn’t truly continue to grow out of control.

    If that is the case, I would dig deeper and look at just what is occurring on your database, look at if you changed the default recovery interval and look at your overall SQL performance and error logs to ensure nothing else is occurring.

    Shoot me an e-mail if you want to chat more or reply in the comments. Either way I am happy to help work through this (not trying to drum up business 🙂 I am happy to help talk through this on my own time)

    Reply
  3. I have been reading on shrinking quite a bit, I am still confused, i do need to implement this on my production server? Can you please provide solution with some script to implement in dev/test and then prod. Ofcourse I am going to go step by step. Please Just give a good solution. Yes Point In Time recovery in prod would be neccessary in my environment. Please advice

    Thanks

    Reply
  4. Hai,

    we have arund 10 clients whose log file keeps increasing…as of now we are detaching and deleting the log file.But after seeing your comments found that is not the correct way of doing it .
    How can i restrict the log file growth.Even in some clients the data file is too large .how to restrict it

    Reply
  5. Pingback: Aaron Bertrand
  6. Okay – shrinking fragments the tables – got that. But then if you rebuild the indexes – it defrags the tables. So now what’s the problem with shrinking. I can’t see anything wrong with it if you do it right, understand what’s happening under the hood, and plan/act accordingly.

    Reply
    • Sure. But why are you shrinking? And won’t it grow again? The goal shouldn’t be a complete lack of free space in the database. For sure – if you have the time and desire to do this – shrink a database, rebuild indexes – rinse and repeat all the time – go for it, but I just don’t understand why anyone would want to do that. Shrinking is generally not something you should waste your time with in production – unless you did something to free a lot of space and want/need to reclaim that…

      Reply
  7. My ldf is over 250 GB my mdf is only 7 GB ,
    Is this waste my diskspace , im ok with it if it is not neccessary to shrink .
    Only one thing is when I back up FULL Disaster recovery with BackupExec it take very long time.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This