I noticed an interesting feature that’s released with SQL Server 2012. In this version now you can set recovery time at database level. In other words now you can set when checkpoint should happen to a database which directly relates to the recovery time of the database. Before stepping into further I would like to explain you what’s recovery time and how does it matter in recovery situation.
Checkpoint is the process which will clear all the dirty pages and hardens the data. Check point can be triggered automatically, manually and also by internal operations like backup, ALTER database command and so on. This process actually moves all the modified data from log file to data file and this is known as recovery time. Based on the checkpoint interval recovery time varies so that during disaster recovery the time need to wait for recovering the database varies.
In the existing versions of SQL Server we can change the recovery time in server configuration (recovery interval (min)) which is effective to all the databases. This doesn’t helped much because if you change this value it’s for all the databases so the database which has less DML activities should also need to go through checkpoint process. Since checkpoint process is an IO intensive operation it’s not recommended to happen very frequently. In other words each database has it’s own DML activities so it’s better to have individual checkpoint interval. Considering this SQL Server 2012 has a new feature called indirect checkpoint which allows user to set individual recovery time.
Below table explains you how indirect checkpoint behaves
|
recovery interval (min) |
Indirect Checkpoint |
Database Recovery |
| 0 (default value) | 0 (default value) | Checkpoint behavior is picked from Server configuration . Default value is 0 which means the checkpoint triggers based on number of outstanding writes |
| > 0 | 0 | Checkpoint behavior is picked from Server configuration. Check point triggers based on the value you give for recovery interval. SQL Server ensures that recovery can be within that time frame. |
| NA | >0 | Checkpoint behavior is independent from server configuration. Checkpoint happens to that database only which ensures that recovery time is less than or equal to the value you specify. |
Enabling Indirect Checkpoint
Let’s see how to enable indirect checkpoint. As you all know all the database configuration can be done through database properties in GUI or using ALTER DATABASE command. TARGET_RECOVERY_TIME is the new parameter that’s included in ALTER DATABASE command which is responsible for triggering indirect checkpoint and this behavior is only for that particular database.
Syntax
ALTER DATABASE DBName SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }
T-SQL
ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 5 MINUTES
GUI
If you note from the image above you can notice that recovery time always in seconds. So if you try to change it in GUI you have to provide seconds value.
Does it work with all recovery model?
Yes off course it will work with all recovery models.
How can I see what are all the databases are enabled for indirect checkpoint?
New column is added to sys.databases catalog view. You can use the query below
SELECT name,target_recovery_time_in_seconds FROM sys.databases WHERE target_recovery_time_in_seconds <> 0
To conclude, this is an interesting feature where you have control on recovery time of individual database. One thing to keep tin mind is that setting frequent checkpoint burdens background IO writer thread but your recovery will be faster during crash. In the other side if you set longer checkpoint interval then your background IO writer will behave normally but there is a increase in your recovery time. So you have to validate the requirement and then balance in between.
Leave a Reply