There are 2 types of DBCC TRACEON (session or global). Session trace is turned on that session only and global trace is visible for all connections on the server.
Trace#: 3502 – This trace writes information to SQL Server log every time a check point occurs
SQLServer 2008 R2 trace flags
SQLServer 2005 trace flags
Example:
-- Turn on trace so SQLServer writes message to SQL Server log everytime check point occurs
DBCC TRACEON(3502, -1)
-- Display which traces are turned on and is it global or session based
DBCC TRACESTATUS(-1)
-- Turn off trace 3502
DBCC TRACEOFF(3502, -1)
Messages recorded in SQLServer log:
2011-03-26 17:28:24.840 spid52 DBCC TRACEON 3502, server process ID (SPID) 52. This is an informational message only; no user action is required.
2011-03-26 17:30:43.300 spid11s Ckpt dbid 5 started (8)
2011-03-26 17:30:43.300 spid11s About to log Checkpoint begin.
2011-03-26 17:30:44.320 spid11s Ckpt dbid 5 phase 1 ended (8)
2011-03-26 17:30:44.320 spid11s About to log Checkpoint end.
2011-03-26 17:30:44.330 spid11s Ckpt dbid 5 complete
alexlima.com 