Share via

CHECKPOINT NOT RAN

Chaitanya Kiran 776 Reputation points
2022-04-03T22:35:24.127+00:00

Log file is full. I checked the log_reuse_wait_desc column in sys.databases, and its value is ""CHECKPOINT".
So,this means checkpoint has not occurred?As per my understanding, checkpoint is a system process and occurs every minute. What is preventing the CHECKPOINT to run?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2022-04-04T02:27:27.64+00:00

    Hi @Chaitanya Kiran ,

    Is this database in simple recovery mode or full recovery mode? Did you do log backup in full recovery mode?

    The Checkpoint Wait means that a checkpoint hasn’t occurred since the last time log truncation occurred. In this situation the virtual log files containing these log records cannot be reused and SQL Server reports CHECKPOINT in the log_reuse_wait_desc column.

    In the simple recovery model, log truncation only happens when a checkpoint completes, so you wouldn’t normally see this value. When it can happen is if a checkpoint is taking a long time to complete and the log has to grow while the checkpoint is still running. I’ve seen this on one client system with a very poorly performing I/O subsystem and a very large buffer pool with a lot of dirty pages that needed to be flushed when the checkpoint occurred. You could also see this if the only checkpoint in the log would be lost if the log was truncated. There must always be a complete checkpoint (marked as beginning and ending successfully) in the active portion of the log, so that crash recovery will work correctly.

    This wait type is usually short lived. SQL Server will automatically create a checkpoint in regular intervals. If this wait type persists, you can execute a checkpoint manually by using the CHECKPOINT statement. You can also adjust the interval between checkpoints, even if only indirectly, by changing the recovery interval server setting.

    Refer to this blog Log Reuse Waits Explained: CHECKPOINT and this blog What is the most worrying cause of log growth (log_reuse_wait_desc)?

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-04-07T12:53:38.75+00:00

    Do you NEED the database in full recovery? Do you NEED point in time recovery? Most of the time you do not, and can just run in simple mode and save a lot of log and backup space.

    https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

    0 comments