Checkpoint in Bulk logged recovery model

Question

Does checkpoint process run when the database is in bulk logged recovery model

Answer 1

Then, why do we have Eager Write process in bulk logged recovery model? I am confused

I will need to admit that I am not overly familiar with the concepts of Eager Writes, but some quick googling tells me that eager writes comes into play with minimally logged operations, so that pages are written to disk directly.

When I think of it seems quite logical. All that is logged is the extent allocation. This means that the data pages must be written to disk before the transaction can commit. Else the D in ACID would up be upheld in case of a crash - rolling forward an extent allocation alone is quite useless.

And it would be quite cheesy if the transaction would have to wait for the Checkpoint process to do its job. Thus, there is all reason to write the pages directly.

However, that does not send the Checkpoint process to the unemployment line, since also when you are in simple or bulk_logged recovery, there are plenty of fully logged operations.

I hope this clears things up for you.

Answer 2

Yes, the CHECKPOINT runs in all recovery models. The main purpose of the checkpoint process is to flush dirty data pages to disk, and that needs to happen regardless of the recovery model.

Answer 3

Hi @Chaitanya Kiran ,

Please see this official document: https://learn.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver15
And this: https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15

Does checkpoint process run when the database is in bulk logged recovery model

YES. As Erland mentioned the CHECKPOINT runs in all recovery models.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.