Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.
If you want to enable/disable Detailed Deadlock Information (1205), use Query Analyzer and DBCC TRACEON to turn it on.
1205 trace flag sends detailed information about the deadlock to the error log.
Enable Trace at current connection level:
DBCC TRACEON(1205)
Disable Trace:
DBCC TRACEOFF(1205)
Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1205,2528)
Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1205,2528)
To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.
DBCC TRACEON (1205, -1)
To enumerate a complete list of traces that are on run following command in query analyzer.
DBCC TRACESTATUS(-1)
SQL Server 2005 Trace Flags.
SQL Server 2000 SP3 Trace Flags. (Undocumented trace flags are not included in document)
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
