Clearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why you shouldn’t use the following method.

USE master
ALTER DATABASE Test SET RECOVERY SIMPLE
USE Test
DBCC SHRINKFILE (Test_log)
USE master
ALTER DATABASE Test SET RECOVERY FULL

This is a piece of code one of my developers wanted to run the other day. Each of you that read it probably fall into one of the following categories.

• A small piece of me just died.
• I shuddered in fear.
• I don’t get it, what’s wrong with that?

If you fell into that third category (or even close) then this post is for you. Falling into the other two categories really just depends on how much of the following information you already know. And if you fall into one of those categories please free to continue reading and let me know if there is anything you see that I missed.

So what’s wrong with it?

I’m going to break this down one line at a time.

ALTER DATABASE Test SET RECOVERY SIMPLE

We are going to assume that your database is in FULL recovery. If it’s not you aren’t doing anything here. If it was in FULL recovery then it was probably that way for a reason. And the reason you use FULL recovery is so that you can do point in time recovery. Generally this is so you can recover as much data as possible in case of a disaster. By switching to SIMPLE recovery you just lost the ability to recover anything between now and the last backup. In fact you won’t be able to recover anything until the next backup. Let’s hope you don’t need that information anymore because it’s gone.

DBCC SHRINKFILE (Test_log)

This shrinks the size of the log file. If what you are worried about is how much available space you have inside the log this has the opposite effect. Typically if your log file has increased to a specific size then you needed that much space. Now that the file has been reduced in size the next time the log needs that additional space it’s going to have to grow. That’s going to take some time and cause you a performance hit. And if your auto-growth settings aren’t sufficient (say the default 10%) you are going to end up with a huge number of VLFs. Let’s say for example your log file started out at 50gb. You shrunk it to 1gb. You run your normal processes, which very quickly require that you have a 50gb log file. While that process was running you had to wait for 42 autogrowth events adding more than 650 VLFs. And in fact you’re now at ~55gb. Even bigger than what you started with.

ALTER DATABASE Test SET RECOVERY FULL

There are several problems at this point. First what happens if your database was originally in SIMPLE recovery? You’ve now set it to FULL and since it was SIMPLE before this you won’t have transaction log backups in place. Because of that you’re going to be getting even more transaction log full errors.

But let’s say you actually checked up front and you were in FULL recovery. Going into SIMPLE and back broke your backup chain. In fact until you take a FULL or DIFFERENTIAL backup you are not going to be able to take LOG backups. Assuming that alerts are set up correctly someone is going to start getting calls to let them know that the backups are failing and they aren’t going to be happy with you. Not to mention until this is fixed you still have no way restore past that last backup. If there is a problem this could be come a career changing event. And not in a good way. Oh, and until the next FULL or DIFFERENTIAL backup is taken and then the next LOG backup after that the log can’t clear out. So again you are going to end up with more log full errors than you started with.

When is it ok

Warnings having been spoken (in a long winded manner) there is a time when this is actually the correct way to handle things. To give a specific example I saw a database with a ~100MB data file, and a ~100gb log file. The log file was so large because log backups had never been set up. I knew the log file didn’t need to be that large and I didn’t have room on the machine to take a log backup of that size (oh, and the FULL backups had been failing too). I spoke with the business and explained the problem and with their permission I altered the database, shrunk the log to a reasonable size, put it back in full recovery and immediately took a FULL backup.

Filed under: Backups, Log File, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, disaster recovery, Log File, microsoft sql server, transaction log