Have you ever tried to capture when CHECKPOINT is happening to the database? If not then you can utilize trace flag 3502 to capture CHECKPOINT information to errorlog. You need to add this trace flag to SQL Server startup parameter to capture these information when CHECKPOINT triggers. This will help you to analyze how frequently checkpoint is happening in the database.
Now let me add this trace flag to startup parameter (requires SQL Server restart) as shown below. To know how to enable trace flag check my article “Enable \ Disable Trace Flag in SQL Server”
Let me run checkpoint manually to cross check whether the same information is logged in errorlog. Below information is captured in errorlog
2011-08-03 22:57:50.620 spid53 Ckpt dbid 5 started
2011-08-03 22:57:50.620 spid53 About to log Checkpoint begin.
2011-08-03 22:57:50.620 spid53 Ckpt dbid 5 phase 1 ended (8)
2011-08-03 22:57:50.640 spid53 About to log Checkpoint end.
2011-08-03 22:57:50.640 spid53 Ckpt dbid 5 complete
CHECKPOINT GO XP_READERRORLOG
Thus you can use this trace flag to capture checkpoint information in errorlog.
Leave a Reply