How to Truncate SQL Transaction Logs

Transaction logs in SQL Server 2012 tend to grow over time, which can sometimes fill all your available disk space. To avoid this, SQL Server has Truncate transaction log operation (free up space in the logical log for reuse of the transaction log). Log files truncate automatically, depending on the recovery model:

  • Simple recovery model — log files truncates after reaching the checkpoint
  • Full recovery model — after a log backup, if a checkpoint has occurred since the previous backup

But there are situations when automatic SQL log truncate job for some reason doesn’t work and logs occupy all available disk space. It always happens suddenly in situations which you are urgently in need for free space.

How to Truncate Transaction Logs on SQL Server  2012?

In this case, this error appears when you are connecting to MS SQL database:

Microsoft OLE Provider for SQL Server: The transaction log for database “YourDBName” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002

This means that drive where the SQL transaction log stored is out of space and SQL cannot write transaction data. In this case, you can truncate SQL logs files manually.

Tip. You need to backup transaction logs on productive SQL server regularly to avoid overflowing the transaction log file and automatic growth operations.

This situation typically occurs when using a Full recovery model. In this model, the log files cannot be cut until all transactions are not present in backup. It is necessary to ensure that you are using a continuous log sequence number (LSN) in the log records. Accordingly, for the truncate you need to make a full backup of the DB, or (easier and faster) temporarily switch it to Simple recovery mode.

To truncate SQL transaction logs launch SQL Server Management Studio (SSMS), select the desired database (with large transaction log), right click on it and select Properties from the context menu. Go to Options and switch database Recovery model to Simple.

sql server truncate log

Then, in the same context menu, go to section Tasks -> Shrink -> Files. In File type select Log, in File name field specify the name of the log file. In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file and click OK.

truncate sql log

After completing operation, change database Restore mode back to Full.

The same can be done from Query Analizer with a simple script (script works starting from SQL Server 2008):

USE ″YourDBName″
 ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE
 DBCC SHRINKFILE (″YourDBName″, ″Desired_size″);
 ALTER DATABASE ″YourDBName″ SET RECOVERY FULL

Tip. After you truncate transaction log and shrink it, be sure to make a full backup of your database.

This is just one of the ways to quickly reduce the size of the logs. Not the greatest, but very simple and effective.

You may also like:

Applications deployment by Make and Model using MD... In this article we will show you guys how to configure your SQL database within MDT 2013 Update 1 and configure it to start using the Make and Model s...
How to Setup FTP Server in Windows 10 Nowadays the FTP technology is awesome and we decided to show how to setup and access an FTP server in Windows 10 in a simple way. How to create FTP ...
Renaming a Computer with SQL Server Instance After SQL Server installation internal SQL Server is based on the host computer name. However, when you are changing the host name, old SQL server nam...
Guide to SQL Server Transaction Log Backup In case you use Microsoft SQL Server you might heard about the Simple and Full database recovery models. Probably you also know that a Simple recovery...
How to Delete IIS Log Files on Windows Server 2012 IIS (Internet Information Services) Web Server on Windows Server generates a sufficiently large amount of log files during its work. The main problem ...
Tags:, ,
  • Johnson Welch

    fantastic article! This is so chock full of users information and the
    resources you provided was helpful to me. There I found a very
    informative blog Preventing Transaction Log Files from Growing
    Unexpectedly. You may also have a look:

    http://www.sqlmvp.org/transaction-log-is-too-big-or-growing-unexpectedly/

  • Kevin McClain

    I thank you so much for this! It’s 3:15 AM CST and I needed to get a job running that was failing because the log file was full. Awesome article and thanks to you bros!!

    • That`s a great news for us, Kevin! Enjoy our website!