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.

Date of Transaction Log File is Unchanged

Transaction log file date is not getting changed at windows level

Last week I came up with very strange experience and I would like to share this here. I was at client place and one day client asked me that application user is not able to see some of the transactions for one of the server. Applications team were sure that some transactions have been done very recently within fraction of seconds.

Let me tell you that this was the server where 10 days back I moved the data and log files to different LUNs and removed the older LUNSs. After that I renamed the new LUNs drive letter as same as older LUNs. I did this to reduce the space available in the LUNs as most of the space was getting wasted due to small size of databases.

Windows team from client went to the server and looked at the transaction log file date and they found transaction log file date at windows explorer never changed from last 10 days. Quickly they thought somehow transaction is not happening at SQL server level and there are some problems with the database.

Client came to me and started saying please check why transaction log file is not getting changed. There is definitely we missed something when we changed the location of data and log files.

This was the first time I heard this. We DBA never think about changing the date at windows level. For us only thing matters are transactions (insert/update/delete etc.) are happening at database level. To prove transaction is happening or not, quickly I created one table, inserted some data, updated something and retrieved the rows from the table. I found everything was fine but the transaction log file date did not change at that time as well. Still it was showing older date.

That time I was sure that there is no problem in the transactions, there are some other issues that application team has to look into. I asked let me know the data criteria that you are trying to retrieve to. I tried the same and SQL server was able to return the data successfully through SSMS.

Now the question is why transaction log file date is not getting changed even though we are doing some transaction in the database. I was having something in my mind and I tried shrinking the log file with very less space (It is not recommended to shrink the log files in Production until and unless you are very sure what you are doing). The moment I executed DBCC SHRINKFILE, I found transaction log file date got changed.

So here is the conclusion: Insert/update may not change the transaction log file date at windows level. The date will only be changed when there are some changes in the physical size of the file (shrink or truncate etc.). If there is not physical change at file level, date will not change.

Also this is just meaningless as far as DBAs are concerned but sometimes we have to answer the clients immediately.

Please let me know if you have any suggestions/questions/feedbacks on this as I heard this first time in my career.

 

 

 

 

 

Why Transaction Log is Growing!!!

Why Transaction Log is Growing!!!

Reasons for Trnsaction Log Growth

Following are the basic reasons for Transaction log growth:

1. When we do bulk insert/update operation.
2. Your database is in Full Recovery model and you are doing Index rebuild operation. Or your database is involved in Mirroring/Log shipping and you are doing Index rebuild operation.
3. If you have Replication implemented in your environment and something is failing in that, you may see transaction log growing unexpectedly.
4. Check the Auto growth value. I have see most of the times high percentage of this causes transaction log growth.

How to Troubleshoot

1. Backup the transaction log if you have enough disk space and your database is not in SIMPLE recovery model.
2. We can user DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the specified database.
3. There could be some transaction running on the server that could be recovered later. Kill those transactions. We can use the command DBCC OPENTRAN.
4. We can try shrinking log file by using command DBCC SHRINKFILE. We may need to user BACKUP LOG with TRUNCATE_ONLY. This is what I use in my QA environment if I face any issues with transaction log growth.

USE <Your DB Name>

GO

CHECKPOINT

GO

BACKUP LOG <Your DB Name> TO <Backup Device Name> WITH NOFORMAT, INIT,  NAME = N'<Your DB Name>-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

DBCC SHRINKFILE(<Name of the log file>, 1)

GO