Hi Folks,

As you would have seen, I have discussed about DBCC previously. But this blog-post is purely on SQL Server DBCC TRACEON.

SQL Server DBCC TRACEON is being used to enable the specified trace flags.

Well, you can see the syntax for the TRACEON:

And for the TRACEOFF:

Here, ‘trace#’ is for the number of trace flag to turn on or off, ‘n’ is the placeholder that indicates multiple trace flags can be specified, ‘-1’ switches off the trace flag globally, and ‘WITH NO_INFOMSGS’ suppresses all informational messages.

DBCC TRACEON returns the following message:

“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

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.

For e.g. , if you want to enable the 1205 Trace Flag, which is being used to send  detailed information about the deadlock to the error log, then write the code:

Now, if you want to disable it, then write the code:

The Trace Flag 3205 is being used to disable the hardware compression for tape drivers, and is being enabled only for the current connection:

Now, if you wish to call it globally or at the server level, then use ‘-1’:

The following example will turn off the trace flag 1205 and 3205 at the same time:

Hence, this was all about DBCC TRACEON.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook