Trace Flags are used to switch On/Off a specific characteristic in SQL Server.
A trace can be either set for global scope or session scope depending upon your choice and requirement.
A Trace flag enabled on the sessional scope will remain enable for the time and scope of that very session only, which has enabled that trace flag.
A Trace flag enabled at the global scope will either remain enabled as long it is not disabled or till the SQL Server services are restarted, depending upon the way it has been enabled. And saying this we come to the question on “How to enable a Trace Flag?”.
To enable the Trace flag on session scope we use DBCC TRACEON command.
When running DBCC PAGE command, it’s output is not visible until and unless you use the TRACE FLAG 3604/3605.
To see which Trace flags are open in the environment we use DBCC TRACESTATUS.
The columns are self explanatory.
Status – 1 for enabled.
Global – 1 if the trace flag is enabled for the global level.
Session – 1 if the trace flag is enabled for the session level.
Here as we have set the trace flag 3604 for this session that’s why we are seeing 1 in session column for 3604.
Note: If we run the DBCC TRACESTATUS from different session we will not see the flag 3604, as being sessional its scope is limited to that session only (54 here).
There can be a requirement when we need a trace flag enabled on the global level, i.e for every sessions. This can done two ways, first by using the DBCC TRACEON command and second by using the startup parameters.
The main difference between these two methods is that by using the command trace flag remains enabled until either someone explicitly disables the trace flag or the SQL Server services are restarted. But when using the startup parameter the trace flag gets automatically enabled whenever the SQL Server services are restarted. So if you want to have a trace flag enabled 24/7 without worrying about the restart of the services use the startup parameter.
To enable the trace flag using command we use DBCC TRACON but this time with ‘-1’. Here we are enabling the trace flag 3605.
Note: This time if we run DBCC TRACESTATUS from different session we will see the trace flag 3605 as it’s global and every session can use the characteristics enabled by it.
As mentioned before the trace flag will remain enabled as long as someone doesn’t disable it or SQL Server services are not restarted.
Global Scope using Startup Parameter.
To enable a trace flag for the global scope and want it to remain open no matter how many times the SQL Server is restarted we use Startup Parameters of SQL Server.
The steps involved in putting a trace flag in startup parameter is:(Here we are enabling the trace flag 3226)
1. Open SQL Server configuration manager.
2. Right click on the SQL Server services. (The instance for which you want to enable the trace flag). And click on properties.
3. Goto Startup Parameters.
4. Enter “-T3226”. And Click Add. Apply
5. Restart SQL Server Services.
Now when you’ll check the SQL Server log, you can see that SQL Server has enabled the trace flag.
Checking the status of the trace flag.
Disabling the Trace Flag
To disable the trace flag DBCC TRACEOFF is used.
Similar to TRACEON when we are disabling a trace flag off global scope we use ‘-1 ‘.
Here we will be disabling the trace flag 3605 which enabled at the global scope.
Checking the current status of trace flags in the env.
Disbaling the trace flag using DBCC TRACEOFF
Checking the status of trace flags.
We can see that Trace flag 3605 is not showing in the status. Which means it is disabled.