Transaction log truncation and log file shrinking for SQL Server

Acronis Backup & Recovery 11.5 does not truncate transaction logs after creating a disk backup. If you do not use the native backup engine of Microsoft SQL Server or any other third-party backup solution that automatically manages transaction logs, you can manage logs by using the following methods.

• Transaction log truncation. Log truncation frees inactive virtual log files (that contain only inactive log records) for reuse by new log records. Truncation may prevent a physical log file from growing but does not reduce its size.

  For more information about truncation refer to the following article:
  http://technet.microsoft.com/en-us/library/ms189085(v=sql.105)

• Log file shrinking. Log file shrinking reduces the physical size of a log file by removing the inactive virtual log files. Shrinking is most effective after log truncation.

  For more information about shrinking refer to the following article:
  http://technet.microsoft.com/en-us/library/ms178037(v=sql.105)

Log truncation by using SQL Server Management Studio

When you switch a database to the Simple Recovery Model, transaction logs are truncated automatically.

1. To switch the database to the Simple Recovery Model:
   1. Run Microsoft SQL Server Management Studio and connect to the instance.
   2. Right-click the database, and then click Properties. This will open the Database Properties dialog box.
   3. In the Select a page pane, click Options.
   4. In the Recovery Model list box, select the Simple model list.
2. The transaction log files will automatically be truncated.
3. Switch the database back to the Full or Bulk-logged recovery model in the same manner as in step 1.

Automating log truncation and shrinking

You can automate the above truncation procedure by using a script and (optionally) add log file shrinking. If you add the script to the Post-backup command, the logs will be truncated and shrunk immediately after a backup. This method assumes that you have Transact-SQL scripting skills and are familiar the sqlcmd utility.

For more information about Transact-SQL and sqlcmd refer to the following articles:

• Using Transact-SQL: http://technet.microsoft.com/en-us/library/ms189826(v=sql.90)
• Using the sqlcmd utility: http://technet.microsoft.com/en-us/library/ms170572(SQL.90).aspx

To automate transaction log truncation and shrinking for an SQL instance

1. Using the following template, create a script that will truncate and shrink the log files for the databases of the instance:

   USE database_name
   ALTER DATABASE database_name SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(logfile_name);
   ALTER DATABASE database_name SET RECOVERY FULL;

   In the last string, the SET RECOVERY value depends on the original recovery model of the particular database and could be FULL or BULK_LOGGED.

   Example for an instance having two databases (TestDB1 and TestDB2):

   USE TestDB1;
   ALTER DATABASE TestDB1 SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(TestDB1_log);
   ALTER DATABASE TestDB1 SET RECOVERY FULL;
   
   USE TestDB2;
   ALTER DATABASE TestDB2 SET RECOVERY SIMPLE;
   DBCC SHRINKFILE(TestDB2_log);
   ALTER DATABASE TestDB2 SET RECOVERY BULK_LOGGED;

2. Add the following sqlcmd command to the Post-backup command:

   sqlcmd -S myServer\instanceName -i C:\myScript.sql

   Where:

   • myServer - name of the server
   • instanceName - name of the instance
   • C:\myScript.sql - path to the script file created in the step 1.

To automate transaction log truncation and shrinking for multiple SQL instances

If you have more than one instance on the machine and want to apply the above procedure to these instances, proceed as follows.

1. Create a separate script file for each instance (e.g. C:\script1.sql and C:\script2.sql).
2. Create a batch file (e.g. C:\truncate.bat) that will contain the commands for the corresponding instance:

   sqlcmd -S myServer\instance1 -i C:\script1.sql
   sqlcmd -S myServer\instance2 -i C:\script2.sql

3. In the Post-backup command, specify the path to the batch file.