SQL Server: Unable to shrink the tlog file?

Who is online?  190 guests and 0 members
home  »  blogs  »  Parikshit Savjani  »  SQL Server: Unable to shrink the tlog file?

SQL Server: Unable to shrink the tlog file?

change text size: A A A

posted  3/10/2012 5:31:20 PM  by Parikshit Savjani -  Views: [10146] 

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!!!
If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
Parikshit Savjani

Twitter: @talktosavjani






 
tags : shrink;tlog file;ldf;dbcc skrinkfile;vlf;fragmentation
  Rate This Blog Entry:  register  or  login

Author

Parikshit Parikshit Savjani (Member since: 1/15/2012 6:38:05 AM)

View Parikshit Savjani 's profile

Comments (7)

Naginder
3/15/2012 6:54:56 AM Naginder said:

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...

by
Naginder
3/15/2012 6:56:52 AM Naginder said:

also the command is dbcc loginfo and not logfile as specified in the post

by
Parikshit
3/16/2012 4:05:27 AM Parikshit Savjani said:

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

 

by
Naginder
3/27/2012 3:10:16 PM Naginder singh virdi said:

gr8

by
Praveen Barath
3/29/2012 7:42:02 PM Praveen Barath  said:

 

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.

 

by
Parikshit
3/30/2012 6:31:39 AM Parikshit Savjani said:

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

 

by
Praveen Barath
3/30/2012 1:39:46 PM Praveen Barath said:

Yupe

 Agreed ….As Its important to know what is ‘not supported’ …Ha ha ha ha.

Cheers mate

 

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Parikshit Savjani's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 206  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 286  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 294  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 171  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 1640  - Comments: 4
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 892  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 356  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 1297  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1681  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1753  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2458  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...