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.

EXEC XP_READERRORLOG

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.

CHECKPOINT;
GO
EXEC XP_READERRORLOG

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