What are trace flags and how are they used?

Problem: What are trace flags and how are they used?

Solution:  DBCC TRACEON

There are some Database console commands which can be used to trace or track the specific character activity and for example I want to trace dead locks that are happening on a SQL server instance I can enable a trace flag and find out the problems instead of setting an extend events or SQL server profiler.

Trace flags can be set on a global level or session level.

Syntax:

DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

To go through the list of flags available, go through this  TRACEON - Trace Flags

Example:

In this example, I would like to show we can capture resources and types of locks that are participating in  deadlocks on a global level.

dbcc traceon (1204, 3605, -1)

go

dbcc tracestatus(-1)

go

Results:

TraceFlag	Status	Global	Session
1204	1	1	0
3605	1	1	0