SQL SERVER – Trace Flags – DBCC TRACEON

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 (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – Trace Flags – DBCC TRACEON

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

    Like

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

    Like

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

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s