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.
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.