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
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)
