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.
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.
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 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 truncated 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 the size of the logs. Not the greatest, but very simple and effective.