In one of my recent interactions with a DBA friend of mine, I had to dig into one of the simplest yet a capability which is available inside SQL Server. SQL Server from time to time has an uncanny requirement to enable certain Trace Flags. Trace Flags are specifically enabled by administrators to mitigate certain behavior that SQL Server has in specific situations. We need to understand that, these trace flags are a special purpose and shouldn’t be used without understanding what the implications are on the server as a whole. So please handle these with care. LEt us learn about trace flags.
Now with this context set let me take a moment about the scenario which my friend was asking. DBA friend of mine was curious to know the trace flags that are enabled on his production servers. He was wanting to know how to get this information easily. I quickly wanted to ask him – “buddy, why are you curious suddenly about this?”. He said, recently he got promoted to as a Senior DBA and the DBA who was managing one of the critical servers had left recently and this server was transferred to him.
My first question to him was, “Have you checked the Server Dashboard reports?”
Just get to the Server node on Object Explorer (SSMS) -> Right Click -> Reports -> Standard Reports -> “Server Dashboard”. Once you are here, you can expand the “Non-Default Configuration Options” and there are these Trace Flags that are enabled “Globally” on a given server.
This was a great start for sure. And there was a silence for a moment.
After this, he quickly bounced back to say that, the DBA had told that there were many other Trace flags that are enabled than what the server did a show on this report. I was pleasantly surprised that I had given him a half-baked solution and had to revert back soon.
DBCC Tracestatus
The best and fastest way to look at all the Trace Flags is to use the DBCC TraceStatus command. There are a number of ways we can look at the statuses using this command. The simplest way to look at all the trace flags enabled is using the DBCC command directly like shown below:
Here we see there are two trace flags that are enabled, one Globally and other Session specific trace flag. As the name suggests the Global flag is applicable for the whole server node while there can be specific status which are applicable just for a session duration.
If you want to check the status of a Trace Flag, then we can call the trace status command specifically using the trace number as indicated below.
I immediately called my friend and was quick in letting him know about this command which has been with SQL Server for a while and that it had missed my mind. This is a quick way to find what are the Trace Flags used within SQL Server using T-SQL commands. If you have any other way to do these checks, please let me know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
