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

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

-- Turn off trace 3502

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.