Acronis Backup for Windows Server Essentials - User Guide - Manual (Page 285)

285 Copyright © Acronis International GmbH, 2002-2014

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:
a. Run Microsoft SQL Server Management Studio and connect to the instance.
b. Right-click the database, and then click Properties. This will open the Database Properties
dialog box.
c. In the Select a page pane, click Options.
d. 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 (p. 122), 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 (p. 122):
sqlcmd -S myServer\instanceName -i C:\myScript.sql