Acronis Backup for Windows Server Essentials - User Guide User Manual

Page 285

285



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

This manual is related to the following products:

Backup Advanced - User Guide