SQL Server DBCC TRACEON

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *