How to Truncate Transaction Logs on SQL Server?

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 namefield 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

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 Shrinksetting 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.