Recovery is writing a checkpoint in database

21 August,2019 by Jack Vamvas

Question: In daily scans of the SQL Server message logs there is an informational message Recovery is writing a checkpoint in database. What does this mean?

Answer: There is normally an added text "No user action is required" - meaning the DBA doesn't need to take any action.  But it's useful  to understand the checkpoint process.

The Checkpoint process writes sql dirty pages (in memory) and transaction logs to the physical disk. Checkpoints are useful as they assist in decreasing the recovery time in a system outage.   The checkpoint process is regularly occuring . 

Different events can trigger a database checkpoint . There are 4 categories of checkpoints 1) Internal (e.g backups in progress, ALTER DATABASE file changes, RECOVERY MODEL changed from FULL to SIMPLE   2) Manual (CHECKPOINT) 3) Indirect 4) Automatic

There are a number of  circumstances that can force a situation where a checkpoint may not be able to be written such as lack of memory,disk or corruption read more  The transaction log for database is full due to ACTIVE_TRANSACTION

Sometimes you may see the SQL Server wait type DIRTY PAGE POLL . The DIRTY_PAGE_POLL wait type is related to the Indirect Checkpoint feature.If you want to read more about whether it's worth worrying about read DIRTY_PAGE_POLL and how to avoid it

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Recovery is writing a checkpoint in database


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer