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://blog.sqlauthority.com)
6 Comments. Leave new
Where can I find a complete list of trace flags?
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
emiliano.gallo@gmail.com
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.