About Trace flags
============
Trace flags are used for temporarily or Permanently(when you specified in startup parameters) i,e It depends on individual decisions for their applications(Note that you need to understand Properly -why you are enabling it & what will be the Impact on that if it is enabled.) to work on the troubleshotting purposes or to track the activities either Globally or session .
Types of Trace Flags?
===============
There are 2 Types of Trace flag -> 1,session Trace flag 2. global Trace flag.
Session Trace flag :It is only for the active connection and that will be visible only for that connection & effect of trace flag will losts once the SPID connections logs out from it's opened session.
(note that It won't affect for other sessions)
Global Trace flag : This will be set at the SQL server instance Level and it will be for all the connections
How to Enable/Disable or ON/OFF trace flags?
============================
To Turn on you have to use
DBCC TRACEON
To Turn off you have to use
DBCC TRACEOFF
How to set on/off the trace flags globally?
===========================
You can use "-1" with your DBCC TRACEON & DBCC TRACEOFF.
For eg:
DBCC TRACEON (4616, -1)
GO
Suppose if you want to enable Multiple trace flags just you can put Comma between trace flags ex :DBCC TRACEON (4616,7806,2528 -1)
To turn off replace ON with OFF.
How to check what traceflags are active/ON?
You can use DBCC TRACESTATUS query to determine which trace flags are currently active whether it is Global/session.
see the below
How to displays the status of all trace flags that are currently enabled globally?
=================================================
You can use :
DBCC TRACESTATUS(-1)
GO
Bottom Lines
==========
1.As per BOL
---------
Some flags can only be enabled as global, and some can be enabled at either global or session scope.
The following rules apply:
A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option.
If a trace flag has either global or session scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
Trace flags are set on or off by using either of the following methods:
Using the DBCC TRACEON and DBCC TRACEOFF commands.
For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON 2528, -1. To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
Using the -T startup option to specify that the trace flag be set on during startup.
The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.
2.Once the trace flag is turned on, it remains On until either you manually turned off or if it is SQL Server restarted.
============
Trace flags are used for temporarily or Permanently(when you specified in startup parameters) i,e It depends on individual decisions for their applications(Note that you need to understand Properly -why you are enabling it & what will be the Impact on that if it is enabled.) to work on the troubleshotting purposes or to track the activities either Globally or session .
Types of Trace Flags?
===============
There are 2 Types of Trace flag -> 1,session Trace flag 2. global Trace flag.
Session Trace flag :It is only for the active connection and that will be visible only for that connection & effect of trace flag will losts once the SPID connections logs out from it's opened session.
(note that It won't affect for other sessions)
Global Trace flag : This will be set at the SQL server instance Level and it will be for all the connections
How to Enable/Disable or ON/OFF trace flags?
============================
To Turn on you have to use
DBCC TRACEON
To Turn off you have to use
DBCC TRACEOFF
How to set on/off the trace flags globally?
===========================
You can use "-1" with your DBCC TRACEON & DBCC TRACEOFF.
For eg:
DBCC TRACEON (4616, -1)
GO
Suppose if you want to enable Multiple trace flags just you can put Comma between trace flags ex :DBCC TRACEON (4616,7806,2528 -1)
To turn off replace ON with OFF.
How to check what traceflags are active/ON?
You can use DBCC TRACESTATUS query to determine which trace flags are currently active whether it is Global/session.
see the below
How to displays the status of all trace flags that are currently enabled globally?
=================================================
You can use :
DBCC TRACESTATUS(-1)
GO
Bottom Lines
==========
1.As per BOL
---------
Some flags can only be enabled as global, and some can be enabled at either global or session scope.
The following rules apply:
A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option.
If a trace flag has either global or session scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
Trace flags are set on or off by using either of the following methods:
Using the DBCC TRACEON and DBCC TRACEOFF commands.
For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON 2528, -1. To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
Using the -T startup option to specify that the trace flag be set on during startup.
The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.
2.Once the trace flag is turned on, it remains On until either you manually turned off or if it is SQL Server restarted.
No comments:
Post a Comment