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  Best Linux Apps

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:

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...
How to Install and Configure vsFTPd on CentOS 7 This time we will take a look on how we can set up vsFTPd FTP server on CentOS 7. FTP will be useful for every organization and services, for example ...
How to Install and Configure DNS Server on Windows... This article is a step by step tutorial on how to install and configure a DNS Server on Windows Server 2012. MS Windows Server 2012 is a great and ...
How to Choose Server for Small Business In this tutorial we will clarify key points that will help you to determine what type of server is the best solution for small business and give a pri...
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...
Tags:, ,