Share via

Target Recovery Time - new database option in SQL Server 2012

  • Article

 

Recently I’ve created a new database on my SQL Server 2012 instance.
I reviewed the new database options before creating.
One of the options that I’d like to share today is “Target Recovery Time” in seconds or TARGET_RECOVERY_TIME.

Target Recovery Time is new in SQL Server 2012. This option helps us to control checkpoints at database level.
When it is set to 0 (zero) the database uses automatic checkpoint for the current database that uses “recovery interval” from server level (sp_configure).
Once TARGET_RECOVERY_TIME changes to a value bigger than 0, the database starts to use it instead of automatic checkpoint and it is called indirect checkpoint.
The value can be specified in seconds or minutes and will define the maximum time to recover the database after a crash.

As this option can affect important things like recovery time and I/O usage, change it only if you fully understand the implications and 100% sure that it will improve your performance.
I strongly recommend to examine this value at a testing environment prior to changing it at production server.

Comments