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:

Disable Trace:

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.

SQL Server 2005 Trace Flags.

SQL Server 2000 SP3 Trace Flags. (Undocumented trace flags are not included in document)

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server DBCC
Previous Post
SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table
Next Post
SQLAuthority.com News – Best SQL Job Search – Best SQL Job List – Find SQL Jobs

Related Posts

6 Comments. Leave new

  • Where can I find a complete list of trace flags?

  • Hi Smark,

    If you read the post above. I have listed links to all the Trace Flags in the post itself.

    Pinal Dave (SQLAuthority.com)

  • That is not a complete list, it doesn’t include flags like 3226 to hide error messages or 3106 which is required when you want to move the system databases. If you know of a complete list please re-post.

  • Correction, 3226 hides successful backup messages

  • Hi:

    I have an sql 2000 server i want to trace the database .

    More explicity i want to monitoring all the TSQL Sentences than the users execute in the database by user or Host_ID without using the SQL PROFILER.

    There are any Function or any query than i can execute i bring me that information similar than the SQL Profiler show??

    Thank very Much

    Emiliano Gallo

  • We have some issue with deadlock coz in a single transaction we r calling around 10 sps.due to that when multiple users try to do the same process,it gets locked.Could you please give any suggestion for this.


Leave a Reply