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:
DBCC TRACEON (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]
And for the TRACEOFF:
DBCC TRACEOFF (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]
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:
DBCC TRACEON(1205)
Now, if you want to disable it, then write the code:
DBCC TRACEOFF(1205)
The Trace Flag 3205 is being used to disable the hardware compression for tape drivers, and is being enabled only for the current connection:
DBCC TRACEON (3205)
Now, if you wish to call it globally or at the server level, then use ‘-1’:
DBCC TRACEON (3205, -1)
The following example will turn off the trace flag 1205 and 3205 at the same time:
DBCC TRACEOFF(1205,3205)
Hence, this was all about DBCC TRACEON.
And also comments on this!!
Regards
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
