Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

DBCC TRACEON (Transact-SQL)

Enables the specified trace flags.

Topic link iconTransact-SQL Syntax Conventions


DBCC TRACEON (trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]
trace#

Is the number of the trace flag to turn on.

n

Is a placeholder that indicates multiple trace flags can be specified.

-1

Switches on the specified trace flags globally.

WITH NO_INFOMSGS

Suppresses all informational messages.

On a production server, to avoid unpredictable behavior, we recommend that you only enable trace flags server-wide by using one of the following methods:

  • Use the -T command-line startup option of Sqlservr.exe. This is a recommended best practice because it makes sure that all statements will run with the trace flag enabled. These include commands in startup scripts. For more information, see sqlservr Application.

  • Use DBCC TRACEON (trace# [, ....n],-1) only while users or applications are not concurrently running statements on the system.

Trace flags are used to customize certain characteristics by controlling how SQL Server operates. Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server. To determine the status of trace flags, use DBCC TRACESTATUS. To disable trace flags, use DBCC TRACEOFF.

DBCC TRACEON returns the following result set (message):

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

Requires membership in the sysadmin fixed server role.

The following example disables hardware compression for tape drivers, by switching on trace flag 3205. This flag is switched on only for the current connection.

DBCC TRACEON (3205);
GO


The following example switches on trace flag 3205 globally.

DBCC TRACEON (3205, -1);
GO


The following example switches on trace flags 3205, and 260 globally.

DBCC TRACEON (3205, 260, -1);
GO


Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.