When control is returned to the caller after a CHECKPOINT statement is issued by an EVENT or other connection, is the checkpoint process complete?
Or is there a chance some thread(s) or fiber(s) or frammiejammie(s) or winkelfrotz(es) are still busy doing checkpoint-ey things?
I am thinking the answers are "no" and "yes" because if you put a bullet in the head of a server (taskkill) immediately after doing a sackload of updates and executing an explicit CHECKPOINT, the subsequent recovery on startup takes some time.
Database recovery in progress Last checkpoint at Wed Jul 20 2011 13:34 Checkpoint log... ... recovery 25% complete ... recovery 47% complete ... recovery 73% complete ... recovery 97% complete Transaction log: foxhound1.log... Checkpointing... Starting checkpoint of "f" (foxhound1.db) at Wed Jul 20 2011 13:43 Finished checkpoint of "f" (foxhound1.db) at Wed Jul 20 2011 13:43 Recovery complete
asked 20 Jul '11, 13:53
When an explicit checkpoint statement completes, the checkpoint has completed and no other async stuff is queued up to be done afterwards. That doesn't mean that some other frammiejammie can't sneak in and modify the file immediately afterwards. Only on shutdown do we guarantee that the file is never modified after the checkpoint completes. In some older versions, it was pretty much guaranteed that the file would be modified immediately after the checkpoint completed if there were any transactions in progress; however, that particularly unfortunate behaviour went away in v10.
answered 20 Jul '11, 14:08
Checkpoints which are done automatically by the server are done asynchronously as a background operation, but if you execute an explicit CHECKPOINT then it will be done synchronously - when the statement returns the checkpoint operation will be complete.
At the time that you see the "Finished Checkpoint of <dbname>" message in the console log the checkpoint operation is complete - all work associated with the checkpoint is complete.
Remember that during recovery the operations are:
Also note that even though you may have just done a checkpoint just before you killed the server, the server must still go through all pages that reside in the checkpoint log to see if there are any pages that must be rolled back as part of step 1 above. If the checkpoint log was large due to a lot of activity on the server prior to going down then the number of pages in the checkpoint log could be large, and thus step 1 may take some time to read all of those pages. I suspect you had a large checkpoint log.