Friday 27 September 2013

Indirect Checkpoints Feature in SQL Server 2012

I found one interesting feature named Indirect Checkpoints in SQL Server 2012 even there are many more so thought to share with you all.  Using this feature a Database Administrator can manually change the target recovery time (seconds) parameter for a particular user database from its default value of ZERO.

Changing the value of Target Recovery Time parameter higher than ZERO changes the CHECKPOINT behavior of that particular database from Automatic Checkpoint to Indirect Checkpoint. When the Recovery Interval (MIN) value is set to ZERO which is the default value then SQL Server Instance will rely on Automatic Checkpoints and when Recovery Interval (MIN) value is higher than ZERO SQL Server Database Engine relies on Indirect Checkpoint for that particular database. Automatic Checkpoints occur for the database approximately once a minute for all the active databases and the recovery time for the database will be typical less than a minute.

Basic of Checkpoint command in SQL Server?

Just to recap what checkpoint command does in SQL Server. SQL Server Database Engine automatically executes CHECKPOINT command to write all the dirty pages from the Buffer Cache of the current database to the disk. Dirty pages are nothing but the data pages which were stored in the Buffer Cache and were modified means data is not same in disk and buffer, however they are not yet written back to the disk. This process helps SQL Server Database Engine to save time during database recovery process in the event of a sudden system crash or an unexpected SQL Server shutdown.

In the previous versions of SQL Server a Database Administrator can go ahead and change the recovery interval (min) value at the instance level from its default value of ZERO using SP_CONFIGURE System Stored Procedure or using SQL Server Management Studio. However, there was no possibility to change the recovery interval for a particular database.

How to configure Indirect Checkpoints on a database :

USE [master]
GO

ALTER DATABASE [SQL2012DemoDB] SET TARGET_RECOVERY_TIME = 10 SECONDS WITH NO_WAIT

GO

Hope you had good learning.


Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)