Checkpoint Running Long Transaction in SQL Server

Synopsis: Dirty page hardening or SQL server checkpoint running long transaction, is the topic users want to know in detail. Therefore, this blog is all about that topic including all the significant aspects that users must be aware of. Reading the entire article can be of good use to SQL Server users.

Table of Content

• Checkpoint Overview
• Checkpoint Long Running Transactions Effect
• When Do Checkpoints Trigger
• Checkpoint Processes
• Impact of I/O Subsystems
• Conclusion

Overview of Checkpoint

A checkpoint is a procedure of writing all stained datafile pages out of the disk and also the procedure writes the dirty pages from the buffer cache to the physical disk and maintains the log records by writing log records from the buffer to a physical file. People address this as Dirty page hardening.

The checkpoints are also helpful at the time of unexpected system shutdown because they reduce the recovery time for SQL Server. Checkpoints in SQL Server categorized into four parts i.e Automatic, Indirect, Manual, Internal. Which you can learn from here. SQL server checkpoint taking long time is a serious issue and this blog is all you need to read for being one step ahead.

Moreover, along with learning steps to restore MDF file to different database or the same one, users must also care about the log files as well. Lets get going.

Effect of Long Running Transaction on Checkpoints

A long running transaction may affect to the working procedure of the checkpoint. Because an active log consists of all uncommitted transactions and these uncommitted transactions intercepts SQL Server to move forwards to the MinLSN.

This can generate two problems:

• If too many uncommitted transactions are performed and the system shuts down after that, the recovery phase takes too much time as the recovery interval option specified.
• The transaction logs may increase in a large amount, because they cannot be truncated in past MinLSN. This occurs, if you are using the simple recovery model and the transaction log truncated on every automatic checkpoint.

When the Checkpoints Are Triggered?

Users must know that this SQL server checkpoint running long transaction might be critical. The SQL Server runs checkpoints automatically at the specific time intervals and also this process runs individually for each database.

• If the database is in autotruncate mode and the log is full, the checkpoint will trigger to free up some space by truncating transaction logs. Nevertheless, if no log space available for new transactions, there may be the long running transaction and no checkpoints triggered.
• If you are the owner of your database, then you can distinctly issue a checkpoint command to trigger a checkpoint in your database.
• SQL Server 2008, provides the feature to run checkpoints in multiple databases through checkpoint command.

Checkpoint Processes

If you are shutting down your SQL Server without using NOWAIT command, then the checkpoint runs in each database on your SQL Server.

An instructable shutdown of SQL Server arises, when you distinctly shutdown your SQL Server until you use SHUTDOWN WITH NOWAIT command. Even it also arises if your SQL Server services are stopped through the Service Control Manager.

The sp_configure Recovery Interval option is used to control the checkpoint frequency. Also, you can use sqlserver.checkpoint_begin and sqlserver.checkpoint_end events to monitor the activities of a checkpoint. This will tell you when was the checkpoint occurred.

When the checkpoint process runs, it runs through buffer pool and nonsequentially scans all the pages. After detecting any dirty page, it looks as if other physically contiguous pages are also dirty. Then it will do a large block write.

The process continues until it scans the last page in the buffer pool. Sometimes, an already written pages may also be dirty again. Therefore SQL server checkpoint taking long time for users. Therefore, they need to be checked again in a second time.

Also Read: Overview for the SQL Server DBCC CHECKDB Command

Impact of I/O Subsystems on Checkpoints

Sometimes, checkpoints may points a major amount of I/O, due to I/O subsystem to get overloaded with writing requests which can influence reading performance. On the other side you can make use of short I/O activities. In SQL Server 2008, you can use command-line option to throte I/O checkpoints.

Conclusion

Checkpoints in SQL Server completes their processes whenever they triggered and they return immediate results whenever they complete. Sometimes, due to I/O subsystem, SQL Server checkpoints running long transactions or they complete before the specified duration.