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).
Note. The transaction log files are required to roll back the database to the previous state. Typically, the journal stores the sequence number, type of change, the operations performed, etc. Most likely, you can do without the transaction log, but then there is the possibility to lose the database in the future in case of failure.
Log files truncate automatically, depending on the recovery model:
- Simple recovery model — log files truncate after reaching checkpoint (the simplest option that requires database administration);
- Full recovery model — after a log backup, if a checkpoint has occurred since the previous backup. This mode provides the best possibility of data recovery after a failure. In the Full mode, the transaction log (LDF) can grow (because the database changes are accumulated in this log).
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 in which you are urgently in need of free space. In this case, you can find *.ldf log files of a very large size on the disk.
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 (using the SQL query or from the Management Studio GUI).
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 the 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. It is possible to change the recovery model of MS SQL Server on the fly, but to reduce the risks, it is desirable to switch the database into read-only mode and perform a backup of the transaction log (if it’s possible).
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 the database Recovery model to Simple.
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.
After completing an operation, change database Restore mode back to Full.
The same can be done from Query Analyzer 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 the transaction log and shrink it, be sure to make a full backup of your database.
In addition, in the properties of the MS SQL database, you can find the option “Auto Shrink”. When you enable the option, SQL Server will be periodically checking the unused space and reducing the size of the database and log files. Microsoft does not recommend using this option for typical databases, and if you decided to use Auto Shrink, your database should be running in Full Recovery mode.
This option is enabled in the database parameters in the Automatic section. Just change the Auto Shrink setting to True. After enabling autoshrink, MS SQL will perform automatic compression only if the unused space occupies more than 25% of the total volume.
This method is applicable to all supported versions of SQL Server: 2005, 2008, 2012, 2014 and 2016.
This is just one of the ways to quickly reduce the size of the logs. Not the greatest, but very simple and effective.
Also, keep in mind that when you execute truncate SQL transaction logs using this manual, all previous backups made in the Full Recovery model can be discarded. That’s why this method should be used only in critical cases when it is not possible to clean up the disk space in other ways.
In the training courses, Microsoft recommends that only the Full mode should be selected for productive databases. However, many advanced administrators deliberately set up Simple recovery mode for their databases. In this case, there is a significant increase in performance for mass insert operations and when working with large binary data, which justifies some decrease in the possibilities of backup and recovery. What is more important for your task – additional recovery options or maximum performance, so decide for yourself.