Target Recovery Time of a Database

Properties screen of SQL Server 2008 R2

Properties screen of SQL Server 2012

Above image is SQL SERVER 2012 properties.

The default value of this option is 0. When it is set to greater than 0 (zero) it uses indirect-checkpoints and establishes an upper-bound on recovery time for this database.

Do not attempt this right away on your production database. Make sure that you try this out on your development server and validate before making it any changes on your production server. Make sure that you do not change this option unless you are 100% sure about what you are doing and understand the risk as well implications.

BEnefit of enabling indirect checkpoint

1) It may improve database recovery time

2) It may reduce checkpoint I/O as it writes continuously pages to the disk in the background