Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

How to shrink the transaction log file in SQL Server

MSSQLTips author Andy Novick By:   |   Read Comments (17)   |   Related Tips: More > Database Administration
Problem

There are times when a SQL Server transaction log file grows so large that it has to be shrunk back to a more reasonable size.  Before shrinking the log file it is necessary to remove the recent transaction log entries from the log.  That use to be done with the BACKUP LOG.... WITH TRUNCATE_ONLY command.  The TRUNCATE_ONLY option was removed in SQL Server 2008.  How do you remove the log entries so the transaction log file can be shrunk?

Solution

In a recent article, Issues with running DBCC SHRINKFILE on your data files wrote about why not to shrink a data file with DBCC SHRINKFILE.  The issues with shrinking data files that I described do not apply to log files, which have a much different structure.  Inside the transaction log are one of SQL Server's internal structures: Virtual Log Files (VLF).   The log is divided into VLF's so that space can be allocated and re-used once the transaction log entries in the VLF have been backed up.  There's a detailed description of Virtual Log files in the article How to determine SQL Server database transaction log usage

To see how many VLF's are contained in a database go to the database run the DBCC LOGINFO command as seen here:

dbcc loginfo('msdb')
FileId FileSize artOffset SeqNo Status Parity           CreateLSN
------ -------- --------- ----- ------ ------ -------------------
     2   253952      8192 31239      0    128                   0
     2   262144    262144 31243      0    128                   0
     2   262144    524288 31242      0    128   96000000012800004
.
. rows omitted
.
     2   524288  22675456 31229      0    128 6733000000065600011
     2   524288  23199744 31230      0    128 6733000000065600011
     2   524288  23724032 31231      0    128 6733000000065600011
     2   720896  24248320 31232      0    128 6733000000065600011
(71 row(s) affected)
DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

Issuing a BACKUP LOG with TRUNCATE_ONLY or BACKUP LOG with NO_LOG use to be a common solution to clear out the transaction log so that it could be shrunk.  It was never a great idea and the article Issues with running backup log with no_log or truncate_only in SQL Server documents why.  In short the right thing to do is to make a real transaction log backup.  That's what the procedure ns_shrink_db_log does.  It makes a transaction log backup and then runs DBCC SHRINKFILE on the log. 

The reason that it's a procedure at all is that sometimes it's necessary to make multiple transaction logs and run SHRINKFILE each time before the log shrinks to the desired size.  To accommodate this reality, ns_shrink_db_log runs in a loop.  There are several parameters this stored procedure takes:

  • @db_name - database name that you want to shrink log file
  • @target_size_mb - the desired size of the tranascaion log
  • @backup_location - location of the backup files
  • @backup_file_name - name for the backup files. As each attempt is made the attempt number is added to the back of the file name along with the standard extension ".trn".
  • @maximum_attempts - governs how many times it tries. 

   Here's the code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ns_shrink_db_log]

  
@db_name SYSNAME = NULL
  ,
@target_size_mb INT = 2
  
, @backup_location NVARCHAR(200) = NULL
  ,
@backup_file_name NVARCHAR(200) = NULL
  ,
@maximum_attempts INT = 10
  
/* Shrinks the log file of @db_name to the @target_size_mb
*
exec [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\'
             , 'scratch_shirnk_backup', 4
************************************************************/
AS

SET NOCOUNT ON

SELECT
@db_name = COALESCE(@db_name, DB_NAME())
    
DECLARE @logical_log_file_name SYSNAME,
        
@backup_log_sql NVARCHAR(MAX),
        
@shrink_sql NVARCHAR(MAX),
        
@checkpoint_sql NVARCHAR(MAX),
        
@db_id INT = DB_ID (@db_name),
        
@start_size_mb INT,
        
@final_size_mb INT,
        
@attempts INT = 0,
        
@recovery_model INT,
        
@recovery_model_desc SYSNAME,
        
@rc INT = 0 -- return code

SELECT @logical_log_file_name = name,
      
@start_size_mb = size / 128
  
FROM MASTER..sysaltfiles
  
WHERE dbid=@db_id AND  fileid=2
  
SELECT @recovery_model = recovery_model  
    
, @recovery_model_desc = recovery_model_desc
  
FROM sys.databases
  
WHERE database_id=@db_id
  
PRINT 'Starting size of [' + @db_name + '].['
            
+ @logical_log_file_name
            
+ '] is '
            
+ CONVERT(VARCHAR(20), @start_size_mb) + ' MB '
            
+ ' recovery model = ' + @recovery_model_desc

IF @start_size_mb <= @target_size_mb BEGIN
   PRINT
'['+@db_name+'] does not need shrinking'
    
END
    
ELSE BEGIN    

          
    IF
@recovery_model != 3
        
AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
        RAISERROR
('Null backup file location or name. aborting.', 16, 1)
        
SET @rc = 50000
        
GOTO get_out
    
END

   WHILE
@attempts < @maximum_attempts
      
AND @target_size_mb < (SELECT CONVERT(INT, size/128) FROM MASTER..sysaltfiles
                              
WHERE dbid = @db_id AND
                                      
name = @logical_log_file_name) -- not target
      
BEGIN

        SET
@attempts = @attempts + 1
        
        
IF @recovery_model= 3 BEGIN
           SET
@checkpoint_sql = 'use ['+@db_name+']; '
                              
+ 'checkpoint'
          
PRINT @checkpoint_sql
          
EXEC (@checkpoint_sql)
            
END
        ELSE BEGIN
           SET
@backup_log_sql =  'BACKUP LOG ['+ @db_name + '] '
                              
+ ' to disk = ''' + @backup_location
                              
+ CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\'
                                    
THEN '' ELSE '\' END
                              
+ @backup_file_name
                             
+ CONVERT(VARCHAR(10), @attempts)
                             +
'.trn'''
          
PRINT @backup_log_sql              

          
EXEC (@backup_log_sql) -- See if a trunc of the log shrinks it.
        
END
              
       SET
@shrink_sql = 'use ['+@db_name+'];'
                      
+ 'dbcc shrinkfile (['+@logical_log_file_name+'], '
                      
+ CONVERT(VARCHAR(20), @target_size_mb) + ')'
      
EXEC (@shrink_sql)
    
END
END

SELECT
@final_size_mb = size/128
  
FROM MASTER..sysaltfiles
  
WHERE dbid = @db_id AND name = @logical_log_file_name
  
PRINT  'Final size of [' + @db_name + '].['
            
+ @logical_log_file_name
            
+ '] is ' +
      
CONVERT(VARCHAR(20),@final_size_mb)
       +
' MB'
    
get_out:
RETURN @rc

The procedure is easy to execute.

EXEC [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\', 'scratch_shrink_backup', 4
 
Starting size of [scratch].[scratch_log] is 16 MB  recovery model = FULL
BACKUP LOG [scratch]  to disk = 'c:\temp\scratch_shirnk_backup1.trn'
Processed 1 pages for database 'scratch', file 'scratch_log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.014 seconds (0.139 MB/sec).
  DbId      FileId CurrentSize MinimumSize   UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
     7           2         256         128         256            128
DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.
Final size of [scratch].[scratch_log] is 2 MB

One thing to remember once you've run ns_shrink_db_log: those backup files are for real.  They're on disk and they should be included in your normal backup procedure.  Most of my clients copy the .trn files off to another server for safe keeping.  If possible the other server should be in a different location to allow for disaster recovery.

Before going out and shrinking the logs in all your databases because it's so easy now, step back and reconsider why you'd ever shrink a log file.  The log file grew for a reason.  Logs grow because transactions modify the database and there's more transaction activity then transaction log backups.  If the logs grew to that size once, isn't it pretty likely that they'll grow again?  In most cases the answer is yes.  If there was something unusual that caused the logs to grow, you might have reason to shrink the log.  However, it's often the case that the logs will just grow back to their previous size. 

One of the events that you'll want to avoid is autogrowth on the log file during the hours that users are using the database.  Log growth is slow and when it's necessary transactions must wait for the file to grow and be initialized.  Log files are not subject to "Instant File Initialization" the way data files are.  You can read about that in the article Configuring Windows Instant File Initialization for SQL Server 2005.

Next Steps
  • Reconsider even shrinking logs.  It's usually not a great idea.
  • When it's absolutely necessary to shrink a log, Use the procedure in this article.
  • I noticed that I referred to four other articles on this site in this one article! MSSQLTips is full of solid information about SQL Server so be sure to at least look at the tip every day and read the ones that seem interesting in more depth.


Last Update: 8/19/2010


About the author
MSSQLTips author Andy Novick
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, August 19, 2010 - 9:07:07 AM - Ramdas Baghel Read The Tip
I would like to know that “How I can Shrink Log File in Simple Recovery Model?” because we can’t perform Tran. Log Backup in Simple Recovery Model.


Thursday, August 19, 2010 - 9:59:30 AM - tcstl Read The Tip
Shrinking logs is a necessary evil.  Many of my index rebuilds can chew up 100-200 gig


Thursday, August 19, 2010 - 10:12:30 AM - Andy Novick Read The Tip
If your database is in Simple recovery model, you don't have to do the transaction log backup.  Just the DBCC Shrinkfile.


Thursday, August 19, 2010 - 10:20:41 AM - Andrew Vogel Read The Tip
I've used the following frequently to get all of the databases in Windows Internals Database or Express instances back to a manageable size before implimenting maintenance plans or 3rd party backups:

EXEC sp_MSforeachdb 'USE ?;
DECLARE logfile nvarchar(255);
SET logfile = (SELECT [name] FROM sys.database_files WHERE type = 1);
DBCC SHRINKFILE(@logfile, 100);';

Applications that install SQL Server as a back end tend to use the FULL recovery by default and SysAdmins rarely know what's going on when their OS partition fills up with those mysterious "LDF" files.  Here's my original post on it.


Thursday, August 19, 2010 - 5:13:13 PM - Jason Wong Read The Tip
http://usa.redirectme.net/repriser/sqlserverpub.html

My code allows you shrink to a percentage and execute it at night. You can then also run re-index by my other code.

 


Friday, August 20, 2010 - 7:46:50 AM - Andrew Novick Read The Tip
Hi, Shrinking database files is generally not a good idea.  I realize it may be needed in some circumstances but it causes near complete index fragmentation because it moves pages one by one from the the back of the file to the first open page.  The Tip Issues with running DBCC SHRINKFILE on your data files shows how bad this can get.

Shrinking log files is a different story and that can be done so long as the file is regrown to a size big enough that there isn't normally file growth.

Andy

 


Friday, August 20, 2010 - 4:05:46 PM - G D Milner Read The Tip
Anyone who routinely needs to shrink their log file should read this first:

Oh, the horror! Please stop telling people they should shrink their log files!

 

 


Monday, October 11, 2010 - 4:31:11 PM - Kevin Read The Tip
Great procedure! I would like to know how I can do the same thing for SS2000 and SS2005 databases. They are still around and run into the same issue.

Thanks!


Saturday, January 01, 2011 - 4:12:27 PM - Andy Novick Read The Tip

To the person who wants to know how to "Shrink the log file in Simple Recovery Model". 

If the file has grown to a size in SIMPLE recovery model, you probaly shouldn't shrink it.  There was a reason why it grew and it will just grow again. 

If you must use dbcc shrinkfile.  The only reason it wouldn't shrink is if there are open transactions.

 

HTH

Andy

 

 


Saturday, January 01, 2011 - 4:14:14 PM - Andy Novick Read The Tip

To Kevin, who want to know how to do this in SQL 2000 and 2005.  You should be able to use this procedure in those versions.  Earlier versions also have backup log with truncate_only, but you really shouldn't use that.  Use this procedure.

HTH

Andy

 


Wednesday, October 24, 2012 - 4:01:24 PM - Jay Kean Read The Tip

Code for SQL Server 2005. Just needed to modify the declares in the proc as you cannot declare them and defined them in the same breath as you can in 2008.

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ns_shrink_db_log]

   @db_name SYSNAME
  , @target_size_mb INT
  , @backup_location NVARCHAR(200)
  , @backup_file_name NVARCHAR(200)
  , @maximum_attempts INT
 
/* Shrinks the log file of @db_name to the @target_size_mb
*
exec [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\'
             , 'scratch_shirnk_backup', 4
************************************************************/
AS

SET NOCOUNT ON

SELECT @db_name = COALESCE(@db_name, DB_NAME())
    
DECLARE @logical_log_file_name SYSNAME,
        @backup_log_sql NVARCHAR(MAX),
        @shrink_sql NVARCHAR(MAX),
        @checkpoint_sql NVARCHAR(MAX),
        @db_id INT,
        @start_size_mb INT,
        @final_size_mb INT,
        @attempts INT,
        @recovery_model INT,
        @recovery_model_desc SYSNAME,
        @rc INT -- return code

SET @db_id = DB_ID(@db_name)
SET @attempts = 0
SET @rc = 0
SELECT @logical_log_file_name = name,
       @start_size_mb = size / 128
   FROM MASTER..sysaltfiles
   WHERE dbid=@db_id AND fileid=2
  
SELECT @recovery_model = recovery_model  
     , @recovery_model_desc = recovery_model_desc
   FROM sys.databases
   WHERE database_id=@db_id
  
PRINT 'Starting size of [' + @db_name + '].['
            + @logical_log_file_name
            + '] is '
            + CONVERT(VARCHAR(20), @start_size_mb) + ' MB '
            + ' recovery model = ' + @recovery_model_desc

IF @start_size_mb    PRINT '['+@db_name+'] does not need shrinking'
    END
   
ELSE BEGIN   

          
    IF @recovery_model != 3
        AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
        RAISERROR ('Null backup file location or name. aborting.', 16, 1)
        SET @rc = 50000
        GOTO get_out
    END

   WHILE @attempts < @maximum_attempts
      AND @target_size_mb < (SELECT CONVERT(INT, size/128) FROM MASTER..sysaltfiles
                               WHERE dbid = @db_id AND
                                      name = @logical_log_file_name) -- not target
      BEGIN

        SET @attempts = @attempts + 1
       
        IF @recovery_model= 3 BEGIN
           SET @checkpoint_sql = 'use ['+@db_name+']; '
                               + 'checkpoint'
           PRINT @checkpoint_sql
           EXEC (@checkpoint_sql)
            END
        ELSE BEGIN
           SET @backup_log_sql =  'BACKUP LOG ['+ @db_name + '] '
                              + ' to disk = ''' + @backup_location
                              + CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\'
                                    THEN '' ELSE '\' END
                              + @backup_file_name
                              + CONVERT(VARCHAR(10), @attempts)
                             + '.trn'''
           PRINT @backup_log_sql              

           EXEC (@backup_log_sql) -- See if a trunc of the log shrinks it.
        END
              
       SET @shrink_sql = 'use ['+@db_name+'];'
                       + 'dbcc shrinkfile (['+@logical_log_file_name+'], '
                       + CONVERT(VARCHAR(20), @target_size_mb) + ')'
       EXEC (@shrink_sql)
    END
END

SELECT @final_size_mb = size/128
   FROM MASTER..sysaltfiles
   WHERE dbid = @db_id AND name = @logical_log_file_name
  
PRINT  'Final size of [' + @db_name + '].['
            + @logical_log_file_name
            + '] is ' +
       CONVERT(VARCHAR(20),@final_size_mb)
       + ' MB'
   
get_out:
RETURN @rc


Wednesday, December 26, 2012 - 3:31:37 PM - Keith Read The Tip

I have a SQL 2008 db that is currently around 130mb; however, the Transaction log file is almost 120gb and is taking up over half of available drive space.  I am pretty sure it is a candidate to shrink?  Looking for opinions before I do it.... Yes?


Thursday, December 27, 2012 - 5:22:59 AM - Jeremy Kadlec Read The Tip

Keith,

I would check the database recovery model first.  I suspect it is Full.  Check out this tip - http://www.mssqltips.com/sqlservertip/1497/selecting-the-sql-server-database-recovery-model-to-ensure-proper-backups/.

If the database recovery model is Full and if there is no need to have it configured to Full for any high availability or disaster recovery needs, then I would consider setting the database recovery model to Simple.  The tip above shows the code to do so.  If you have a need to use the transaction log for high availability or disaster recovery needs, then I would correct that issue.

Once you have addressed these items, then I would shrink the database transaction log.

HTH.

Thank you,
Jeremy Kadlec
Co-Community Leader


Saturday, February 09, 2013 - 3:20:16 AM - Kamal Read The Tip

 Dear sir ,

 

 i want to delete sql traction log files is it possibal and not effect data .

 if possibal then send me step by step process how to delete  that fils .

 

thanks 


Thursday, March 14, 2013 - 8:03:05 AM - Ray Humphrey Read The Tip

Wow! Excellent! This log was getting way out of hand on my development machine and nothing I tried was working.

 

Thank you very much.


Wednesday, October 02, 2013 - 5:06:56 PM - Peter Read The Tip

Andy,

This is great!!  I tried ti and it worked.  Is there a need to backup the database before or after the shrink? What we usually do is to change the mode to simple then shrink.  In you procedure we dont have to do it is this safe?

Thanks,

Pete

 


Tuesday, January 28, 2014 - 8:36:07 PM - Joe Read The Tip

One question that I have in regards to running this stored proc...

Does the application that is writing to the database you are shrinking the log for services need to be brought down in order for the Shrink to be successful?

I've found that Shrinking without bringing the services down is rarely successful in an application that is writing to the logs frequently.

Thanks,

Joe



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.