SQL Server checkpoints

I was having a conversation with someone over a disgusting vanilla latte and we talked about shutting down a machine and how to confirm if SQL Server starts to checkpoint the databases on the server – obviously it makes sense why it needs to do this but how do we confirm it?

Via Configuration manager I enabled trace flags 3502 and 3605 – both needed to get the checkpoint information and write it to the error log.

I then shutdown the machine, on start-up I looked into the error log.



Notice the ‘s’ in front of the spid<number>? Well that means the checkpoint was done via the automatic process; if you do a manual checkpoint it won’t see this letter.



If you want to know what is actually being written (number of Bufs etc) then that is trace flag 3504.



1 thought on “SQL Server checkpoints

  1. Pingback: Confirming Checkpoints – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s