Truncate and Shrink Transaction Log in SQL Server

Transaction Log Truncation and Shrinking in SQL Server

Let’s understand the concept of transaction log truncation and shrinking in SQL Server.

Transaction log is very important and critical in SQL Server especially when we talk about the truncation and shrinking. Let me share something about these 2 terms so that it will be very much clear for most of the DBAs. I am just guessing you already know about the transaction log architecture. Transaction log internally contains the Virtual log files for the processing of transactions. This section never talks about the complete architecture of transaction log. Please refer SQL server Books line to have very good picture of the architecture.

Let’s categorize this based on Recovery Models. As we know there are 3 recovery models available in SQL server Simple, Full and Bulk Logged. The major difference between all of them is related to the transaction log behaviour. Let’s talk one by one.

Simple Recovery Model:

In Simple recovery model when checkpoint occurs, the virtual log files for which the transaction has been committed becomes reusable for other transactions. Remember all transaction gets stored in Virtual log file with LSN number. Once the transaction gets committed, SQL server makes those virtual log files reusable for another transaction. In Simple recovery model this gets handled by SQL server whenever checkpoint occurs. This makes sense as these virtual files, for which transaction has been committed, are not required for rollback. And hence SQL server makes these virtual files reusable. This is called TRUNCATION of log.

Truncation of log is nothing but actually truncation of active portion of the transaction log. The PHYSICAL SIZE of the log remains unchanged until you specify Auto Shrink Option ON for the database. Most important in SIMPLE recovery model this is handled by SQL server automatically after every checkpoint.  

So the summary is when the transactions are committed/Rolled back, SQL server can reuse the space occupied y those transactions after every checkpoint.

Full or Bulk Logged Recovery Models:

In full or bulk logged recovery models, virtual files never get reused by SQL server until you take the transaction log backup. After every checkpoint, SQL server will write all the dirty pages to DISK but will never make the virtual log file reusable. If you take the full backup of the database then also those virtual log files will not become reusable. In other words Truncation of log will not happen. This gets happened when we take the transaction log backup. The moment we take the transaction log backup, transaction log will be truncated (Active portion of log will get truncated but no change in the PHYSICAL SIZE). Means after transaction log backup, virtual log files for the committed transactions will become reusable.

Sometimes your transaction log never gets truncated even though you have taken the transaction log backup in full or bulk logged recovery model. The only reason could be for this is active transaction due to transactional replication or long running transactions. You can find out the details by using below query.

 

SELECT name, log_reuse_wait_desc FROM sys.databases
 

If the log_reuse_wait_desc display ACTIVE_TRANSACTION, means there are some active transactions which are not allowing your logs to truncate. If it says LOG_BACKUP, means you have to take the transaction log backup.

Remember when we have database in full or bulk logged recovery model, sometimes log never grow. This is because you would have not taken the full backup of the database yet. If you do not take the full database backup, this is exactly same as SIMPLE recovery model. After every checkpoint transaction log gets truncated. Once you take the full backup, it works exactly as mentioned above.

Now let’s talk about SHRINKING of transaction log. Shrinking means reducing the physical size of the file. How to do this and how SQL server decides which part of the log file should be shrinked?

First of all let me talk about the SQL command that could be helpful for shrinking the files. I will not talk about the shrinking database here instead only about shrinking transaction log file.

We use DBCC SHRINKFILE command to do so. Below is the syntax. The first parameter is logical name of the log file and second parameter is size in MB up to which you want to shrink the log file.

 

DBCC SHRINKFILE ('DatabaseName_log', 2)
 

Remember transaction log can only be shrunk up to the boundary of a virtual log file. You can use DBCC LOGINFO command to get the information about virtual log file. So if you find that you cannot shrink the transaction log to a particular size, run DBCC LOGINFO and check the size of the virtual log files to know how large each of them are, and to what sizes you can actually shrink the file to. Wherever you see status as 2 that will be your boundary up to which you can shrink the log file. If you want to shrink more then take the transaction log backup and if your log gets truncated, your virtual log file boundary will change again and DBCC SHRINKFILE will shrink the file till last boundary.

For example, assume that a 600 MB log file that contains six virtual log files has a logical log that starts in virtual log 3 and ends in virtual log 4 when you run a DBCC SHRINKFILE statement with a target_size of 275 MB, which is three-quarters of the way into virtual log 3:

 

Virtual log files 5 and 6 are freed immediately, because they do not contain part of the logical log. However, to meet the specified target size, virtual log file 4 should also be freed, but it cannot because it holds the end portion of the logical log. After freeing virtual log files 5 and 6, the Database Engine fills the remaining part of virtual log file 4 with dummy records. This forces the end of the log file to the end of virtual log file 1. In most systems, all transactions starting in virtual log file 4 will be committed within seconds. This means that the entire active portion of the log is moved to virtual log file 1. The log file now looks similar to this:

 

The DBCC SHRINKFILE statement also issues an informational message that states that it could not free all the space requested, and that you can run a BACKUP LOG statement to free the remaining space. After the active portion of the log moves to virtual log file 1, a BACKUP LOG statement truncates the entire logical log that is in virtual log file 4:

Because virtual log file 4 no longer holds any portion of the logical log, you can now run the same DBCC SHRINKFILE statement with a target size of 275 MB. Virtual log file 4 is then freed and the size of the physical log file is reduced to the size you requested.

A shrink-file operation can remove only inactive virtual log files. If no target size is specified, a shrink-file operation removes only the inactive virtual log files beyond the last active virtual log file in the file. If a target size is specified, a given shrink-file operation removes only enough inactive virtual log files to approach but not exceed the target size. After shrinking, the log file is typically somewhat larger than the target size, and it will never be smaller. The virtual log files make it difficult to predict how much the log file will actually shrink.

That’s all about the transaction log truncation and shrinking I can think of. To summarize truncation is nothing but truncation of active portion of the transaction log but no change in the physical size of the file. Shrinking means changing the physical size of the file by DBCC SHRINKFILE command. How it works completely depends on the Recovery model what you have for your database.

Hope this is helpful.