About transaction log and its truncation in SQL Server

Introduction

From time to time different discussions pop up concerning truncating the transaction log in SQL Server. The truncation is often offered as a treatment to a problem that the log file filling up or is already filled. While it’s true that truncating the log and shrinking the log file may help to continue the database usage, the actual problem is elsewhere and proper steps should be taken before and after the truncation.  

So what is the transaction log 

As the name implies, transaction log is used to store information about ongoing and past transactions and their states. From the recovery point of view, this is one of the most important files in your database. With the information in the transaction log you can recover the database to the latest situation, in case of a disaster. Also, you can recover the database to a certain point-in-time if needed, disregarding later operations.

The log file itself (or files) is used circularly. This means that the log file is filled from the beginning but when the end of the file is reached, the log information is written again starting from the beginning of the file. This happens only if there is usable space in the beginning. If there is no room in the file, the file is expanded, if possible. The file expansion is limited by the auto growth settings of the file and of course by the remaining disk space.

The log file content is logically categorized into three different categories:

• used portion
  This contains log records that are written to the transaction log but can be removed
• active portion
  This is the part of the transaction log which is defined by the oldest active transaction. This information cannot be removed while the transaction is still active
• unused portion
  This is empty space

When log records are removed from the transaction log, the used portion is cleared. The active portion still remains for example for possible rollbacks etc. The log records are removed from the log either by a checkpoint or a log backup operation.

Recovery models 

With recovery model the administrator basically defines what can be recovered in case of a disaster. There are three different recovery models: Full, Bulk-logged and Simple. This article briefly discusses about Full and Simple recovery models. 

Simple recovery model  

Simple recovery model is the ‘easiest’ to use. From backup point of view a full backup needs to be run every once in a while. The full backup in this context refers to the whole database backup or backing up all read-write files at the same time. When the recovery is done, the contents of the database is restored as it was at the moment in the end of of the backup. With Simple recovery model, this is the 'best' that can be achieved, so taking full database backups often enough is crucial. 

From the transaction log point of view using Simple recovery model means:

• The transaction log is cleared (the part that can be cleared) every time a checkpoint is occurring
• Log backups cannot be taken 

So if using Simple recovery model, the transaction log is cleared often and it shouldn't grow too much. The downside is of course that no actual recovery can be done, the database can only be restored.

Full recovery model 

In full recovery model the most important difference is that when a checkpoint occurs, transaction log is not cleared. Instead, new log records keep on accumulating into the transaction log as changes occur in the database. The proper way to clear the transaction log is to take successful log backups regularly. A successful log backup operation clears the used part of the transaction log at the end of the log backup operation. The difference to the previously mentioned checkpoint is that now the modifications to the data are safe with the backup. 

The log is growing too much 

Now this is the problem which is often referred. The log keeps on growing or has already grown too big to tolerate. Based on the previous description you should first check the recovery model. So here’s a small checklist:

• Are you using Simple recovery model? 
• The transaction log should be cleared automatically on checkpoints
• Do you have long transactions?
  Remember, that the active part of the log starts from the beginning of the very first active transaction, even if it’s for example one week old. This part of the log cannot be cleared. 
• Are you using Full recovery model? 
• Have you defined transaction log backups? 
• Have the transaction log backups succeeded? 
• Are the transaction log backups run often enough? 
• And the same question as earlier, do you have too long transactions? 

Those questions should give you clearer picture what’s actually causing the situation.

If the log file isn’t full but has been growing too much, the first option is not to truncate it. Instead, if you’re using Simple recovery model, you should investigate the long running transaction and search for possible flaws in the applications using the database. 

If you’re using Full recovery model then concentrate on the transaction log backups, possibly something is wrong with them.  Of course, a natural question is, do you need the Full recovery model? If this is a production environment, the answer is most likely yes. However, if the ability to recover to the last point in time (or a specific point in time) isn’t needed then you can consider changing the recovery model to Simple.  

The log is full 

This is a more serious situation especially if you’re using Full recovery model. When the log becomes full and cannot grow anymore typically an error <a href="http://msdn.microsoft.com/en-us/library/aa337278(v=sql.105).aspx">9002</a> is raised. For example:

Msg 9002, Level 17, State 4, Line 3
The transaction log for database 'Database name' is full due to 'ACTIVE_TRANSACTION'. 

This means, that operations that change the state of the database cannot be successfully executed until sufficient free space in the transaction log is available. But, this also means that there’s no guarantee that all the necessary information was recorded into the transaction log.

The previous check list applies also this time but since the log is really full, you should also check the auto growth setting of the log file(s). Is it properly defined or should the log be let to grow more? 

Regardless of the decision to let the log grow more or not, the log can be truncated in a situation like this. In earlier versions of Sql Server this is done by issuing special log backup command:

BACKUP LOG WITH TRUNCATE_ONLY; 

Basically this does the same thing as a checkpoint in Simple recovery model; What can be removed is thrown away to gain free space.

In Sql Server 2008 and newer versions this command has been discontinued and instead you have to actually change from Full to Simple recovery model momentarily. Using Transact-SQL this can be done by issuing

ALTER DATABASE <database name> SET RECOVERY SIMPLE; 

Truncating the log (one way or another) also means that in Full recovery model, the log chain is broken. Well, actually the log chain was already broken at the moment the log became full. But nevertheless, in Full recovery model, you must not stop here.

To be able to recover the database to the last point in time the log chain must be intact. Log chain means that all the operations that have been changing the content of the database files are recorded in the transaction log and therefore they can be found in the log backups. Using log backups, log records can be used to recover the database. Since the transaction log became full, something is most likely lost and since the log was truncated, something is definitely lost.

Next steps 

If you were using Full recovery model and it was switched to Simple to clear the log, don’t forget to turn it back to Full, unless your decision was to use Simple recovery model from now on. To set the recovery model to Full in newer version of Sql Server, issue

ALTER DATABASE <database name> SET RECOVERY FULL;  

After truncating the transaction log, consider if the log file should be shrunk or not. If you’re using Full recovery model and decided to let the file grow a bit further, it wouldn’t make sense to shrink the log. On the other hand, if log backups are run more often, it may make sense to shrink the file in order to gain more disk space.

Especially, if the decision was to change from Full to Simple recovery model permanently, the log will not likely grow so much so shrinking is justified. 

Shrinking the log file is done using DBCC command. In short, to shrink the log, issue:

DBCC SHRINKFILE (<name of the logfile>, <target size>) 

And the last, but most important step is to ensure that you have a valid situation in your backup history. As mentioned, if Full recovery model is used, to be able to recover the database, you need a valid full database backup upon which log backups can be applied. Now, since the previous log backup chain is unusable over the point where the log became full, you need a new starting point for possible recovery.  

Typically the easiest way is to take a fresh full database backup. If you have a predefined job for the task, you can run that or issue the command manually. 

After successfully going through all of these steps, you should have a valid situation again and to be able to sleep well. As always when something special has happened or you have any doubt regarding the backups, it's advisable to test the recovery from the latest backups in order to verify their integrity. 

References 

More information about the commands mentioned in this article:

• BACKUP 
• ALTER DATABASE 
• DBCC SHRINKFILE  

History

• 8^th May, 2012: Created.
• 9^th August, 2012: Minor corrections and formatting