Microsoft® SQL Server® 2012 Bible

528

Part V: Enterprise Data Management

To set media retention to 10 days in code, do the following:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'media retention', 10;

RECONFIGURE;

Backup Compression

Backup compression is a feature introduced in the SQL Server 2008 Enterprise Edition.

Although initially introduced as an Enterprise Edition feature, starting with SQL Server

2008 R2 backup compression became supported by the Standard and all higher editions.

In Management Studio, to compress new backups by default

set backup compression default by checking the Compress backup check box on the

Server Properties Database Settings tab (refer to Figure 19-12).

To set backup compression default in code, do the following:

EXEC sp_configure 'backup compression default', 1

RECONFIGURE

After installation, new backups are uncompressed by default. Backup compression can greatly reduce the backup

sizes and backup/restore times. But this improvement is not free. Backup compression signifi cantly increases the

CPU usage that may impact other operations on the server. Hence, you should test this feature thoroughly to under-

stand the pros and cons before implementing in your production SQL Server.

For more information about backup compression, refer to the Chapter 21.

Recovery Interval

The recovery interval server confi guration option controls when SQL Server issues

checkpoints for each database. A checkpoint fl ushes dirty pages from the buffer cache

of the database to disk. Checkpoints are done when SQL Server estimates that the recov-

ery time will be longer than the specifi ed recovery interval. The estimated duration

applies only to the REDO (roll forward) phase of the recovery and not to the UNDO (roll

backward) phase. The default value for this option is 0 , which implies that this option is

automatically confi gured by SQL Server.

Best Practice

Leave the recovery interval option to the default value of 0. If you do change the

recovery interval, then be sure to test it thoroughly and evaluate all other performance tuning

opportunities fi rst.

c19.indd 528c19.indd 528 7/30/2012 5:43:14 PM7/30/2012 5:43:14 PM

http://www.it-ebooks.info