SQL Server: Unable to shrink the tlog file?

This is the most common question I come across from some of the inexperienced DBAs in SQL Server or experienced Oracle DBAs who are administering SQL Server under the assumption that SQL Server Tlog files behave similar to Oracle Redo log files and doesn’t require any administration. Although the answer of this question is nicely documented in Book Online but we still continue to see this question coming up. So I thought this question deserves a blog post to cater to wider audience.

Before you consider shrinking your T-log file, one of the questions you need to answer yourself is why the T-log file grew in first place. If the T-log file is growing everyday by some amount then identify why the T-log file is not truncating.

One of the catalog view which is introduced starting SQL 2005 can help us identify the cause of the T-log not getting truncated preventing the tlog to be reused.

select name,log_reuse_wait_desc from sys.databases

For further details on how to identify cause of delay for truncating the Tlog file. Please refer to the article here.

So ideally, you do not want to see auto-growth of the tlog file (although you will turn Autogrowth ON for cases of emergency). Since too many auto-growth of tlog file causes VLF Fragmentation which is very descriptively blogged by Kimberly Tripp here.

Further if you are observing the Tlog growing everyday it doesn’t make sense to shrink it if it grows back again the following day since frequent shrink and growth causes Disk fragmentation and causes WRITELOG waittype for the SQL instance.

So assuming that Tlog grew due to some known activity on the server (such as index reindexing) or your unawareness to take backup of the tlog when the database is in full recovery mode, Now either the activity is completed or you took the Tlog backup and hence the Tlog was internally truncated.

You can confirm the internal truncation of the tlog file using following command

DBCC SQLPERF(logspace)

This above command will tell you the amount of the tlog file used and hence the percentage of the free space in the tlog file.

You assume that since you have 80-90% freespace in your tlog file, you should now be able to shrink the tlog file and reduce it to smaller value and hence you try to shrink the tlog file either by using SSMS or by using the Tsql command.

DBCC SHRINKFILE(2, target_size_in_MB)    -- (2 is the fileid for tlog file)

However you are unable to shrink the tlog file even though you see freespace available in Tlog file and following is the reason why?

As documented in Books Online here,

When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual log files from the end of the log file are freed to reduce the log to

the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log file boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file that contains six 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.

To see if the end of the tlog file contains an active VLF , you can use the following command in the context of the database whose tlog file you want to shrink

DBCC LOGINFO

Each row in the output corresponds to a VLF in the Tlog of that database. So if you see 80 rows, it indicates Tlog file is divided into 80 VLFs . Further in the output you see a status column which indicates where the VLF contains any active transaction.

Status

0              inactive VLF

2              active VLF

If you observe the last VLF of the Tlog file with the status of 2, you will be unable to shrink the tlog file even status of the VLF is 0 for the rest of the tlog file. In other words you will not be able to shrink the tlog file until the last VLF is active VLF (with status 2).

Until SQL 2005, we had a command BACKUP LOG ….WITH TRUNCATE_ONLY which was used to shift the active VLF at the start of the tlog file thereby allowing the tlog file to be truncated. But it breaks the Tlog backup chain which makes the command very dangerous.

Hence starting SQL 2008, the above command is discontinued and you will not be able to fire this command.
The recommended and supported method to shrink the tlog file in this case is by switching to Simple recovery model which is cause all the VLFs to become inactive immediately and followed by shrinking of the tlog file.

Method 2 (by Naginder)

================

Instead of changing to simple recovery, we can take a tlog backup and shrinking the tlog file using dbcc shrinkfile in a Batch. Doing this is in a batch is very important else by the time we do dbcc shrinkfile, there may be transactions being posted which will update the last VLF and hence shrinking wont be possible.

The method 2 suggested by Naginder should be ideally used for shrinking tlog file but many a times in Production environment, you cannot predict the number of the tlog backup you need to take followed by DBCC SHRINKFILE and hence some people would go for switching to Simple Recovery Model, Nevertheless this would be recommended method which can prevent you from taking unnecessary full database backup.

To conclude we should first try to take the tlog backup as suggested by Naginder and if that method doesn’t work, you can switch to simple recovery model

Hope this helps!!!

Regards

Parikshit Savjani

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter | Follow me on FaceBook