sql server 2012

How to Truncate SQL Server 2012 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 a log truncating 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 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.

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 transaction log stored is out of space and SQL cannot write transaction data. In this case, you can truncate transaction log files manually.

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.

READ ALSO  Windows Defender Error Code: 0x80073afc

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

database properties recovery model

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.

shrink file

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 and shrink the transaction log in Microsoft SQL Server, be sure to make a full backup of your database.

This is just one of the ways to quickly reduce 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...
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 ...
How to create your own Cloud Storage Server In this tutorial, we are going to show you how to create your own cloud storage server. Head over to http://www.cloudways.com/ and click on Free Tr...
How to Setup an FTP Server in Windows 10 Today we will show you how to setup and access an FTP server in Windows 10. At first let’s create an FTP folder. This folder will be accessed by our F...
Tags:, ,