sql server 2012

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.

READ ALSO  How to create your own Cloud Storage Server

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.

READ ALSO  How to fix Internet connection problem for Windows 10 users

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...
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 ...
How to Install and Configure IIS Web Server with P... This guide will show you how to deploy your own IIS Web server, and enable it to run PHP on Windows 8 / Windows Server 2012. Thereafter this platform ...
Tags:, ,