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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

7 Comments on “SQL Server: Unable to shrink the tlog file?”

  1. This is a wonderful post, and i have been doing this whenever i face this problem of “unable to shrink the tlog file”. Just one addition to this, 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.

    Pls correct me if i am wrong…

  2. Thanks Naginder for correcting the post. I have included your method in the blog post alongwith my comments.

  3. Nice Topic…….

    Very well said Parkshit, As you covered almost everything including the link of Too Many and Too few VLF could be bad…Appending and adding to the post.

    “LOG MANAGER” in SQL Server 2005 is the Brain for Log files which is smarter, faster than SQL 2000, So. YES, You can perform one DBCC SHRINKFILE statement to shrink the transaction log file immediately to 2 virtual log files. You can do this because the SQL Server 2005 log manager creates 2 virtual log files by following the physical disk storage order. Both of these virtual log files are at the start of the transaction log file.

    When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size.

    For more details (Ref) – http://support.microsoft.com/kb/907511

    One more link I wrote long time back for SQLAUTHORITY.com

    http://blog.sqlauthority.com/2007/11/22/sql-server-shrinking-truncate-log-file-log-full-part-2/

    How to truncate log file in SQL Server 2005

    SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.

    Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.

    I. Shrink the log file size at the right time

    I found out this trick:

    Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files

    set the file size, say, 1MB.

    Then, click OK and you are done.

    II. Eliminate the log file completely

    Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

    a. Detach the database

    b. Rename the log file

    c. Attach the database without the log file

    d. Delete the log file

    Let’s say, the database name is testDev. In the SQL Server Management Studio,

    Highlight the database-> Tasks->Detach..-> Click OK
    Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
    Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
    After this is done, you can verify the contents of the attached database and then delete the log file.

  4. Praveen,

    Good to see your comment 🙂 … its long time we had a call Smile

    The only challenge here is, it is not a supported method of Microsoft and it may not work in future versions and hence I would not like to add it to the Blog Post.

    If we can achieve the same results by using one of the supported Method then we dont want to promote unsupported methods

Leave a Reply

Your email address will not be published. Required fields are marked *