Quick one today:
Trace Flags are a unique feature in Sql Server. They allow enabling certain features, that could change the way Sql Server behaves. Generally, these are helpful in troubleshooting any uncommon scenarios or identifying performance bottlenecks.
There are two types of trace flags. Global and Local (session). Global trace flags are enabled globally for the entire instance; Local flags are applicable just to the current session. Some trace flags are global flags (ex: 1204), others are local trace flags; We also have some that could be enabled either globally or locally (Ex : 4199).
To check what TRACE FLAGS are enabled on our instances there are several ways to accomplish this, so here we’ll look at a couple of them. Following scripts allow us to check the status of any status flag; If they are enabled locally (for session) or globally.
-- -- Check if the trace flags are enabled globally -- DBCC TRACESTATUS(1204, -1) GO DBCC TRACESTATUS (1222, -1) GO DBCC TRACESTATUS (1117, -1) GO -- -- Or run this alternative -- DBCC TRACESTATUS(1204, 1222, 1117, -1) GO
ENABLE TRACE FLAGS
Now, use the following code to enable them globally. Trace flags 1204 & 1222 are used to troubleshoot deadlocks. For more information on types of deadlocks, please review this post. And techniques to capture deadlock traces, please review this post.
-- -- Enable Trace Flags globally -- DBCC TRACEON (1204,-1) GO DBCC TRACEON (1222,-1) GO
Please note that if the instance is restarted these settings will not be in place after restart. For that we need to enable them during startup, as part of startup parameters.
DISABLE TRACE FLAGS
Similarly, when you are ready to turn them off, use the below script:
-- -- Turn trace flags off globally -- DBCC TRACEOFF (1204,-1) GO DBCC TRACEOFF (1222,-1) GO
Hope this helps,