Categories
General

Day 4–Trace Flag 3502–Log Checkpoint information to errorlog

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

trace_flag_3502_1

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

trace_flag_3502_2

Thus you can use this trace flag to capture checkpoint information in errorlog.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*