Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to shrink the transaction log



By:

One thing that I see a lot of administrators ask about is transaction log size and how to truncate it. Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model). MSSQLTips.com offers plenty of tips regarding transaction log truncation, but Iíll show you two ways to shrink the log.

Shrink the log in SQL Server Management Studio

To shrink the log in SSMS, right click the database, choose Tasks, Shrink, Files:

Shrink the log in SQL Server Management Studio

On the Shrink File window, change the File Type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup:

On the Shrink File window, change the File Type to Log.

Shrink the log using TSQL

If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file:

        DBCC SHRINKFILE (AdventureWorks2012_log, 1)
        

Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you donít care about losing the data in the log.

        ALTER DATABASE AdventureWorks2012
        SET RECOVERY SIMPLE
        GO
        DBCC SHRINKFILE (AdventureWorks2012_log, 1)
        GO
        ALTER DATABASE AdventureWorks2012
        SET RECOVERY FULL
        

**You can find the logical name of the log file by using the following query:

        SELECT name FROM sys.master_files WHERE type_desc = 'LOG'
        

Another option to shrink the log using the FULL recovery model is to backup the log for your database using the BACKUP LOG statement and then issue the SHRINKFILE command to shrink the transaction log:

        BACKUP LOG AdventureWorks2012 TO BackupDevice
        

Last Update: 3/25/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools