Today we are going to learn about a very simple and easy thing. If you are using SQL Server for a while, you may be familiar with the concept of trace flags. Trace flags are special commands which enable certain specific behavior of the SQL Server. Whenever I go to  Comprehensive Database Performance Health Check one of the first things which I check is if any specific trace flag is enabled on the server or not. Let us learn how we can do that with TRACESTATUS.
First, let us enable the following trace flag.
DBCC TRACEON(3608)
Now that this trace flag is enabled, let us run the following command to check the status of the same.
DBCC TRACESTATUS()
As we have already enabled a trace flag 3608, the query above will return the following result. DBCC TRACESTATUS returns a column for the trace flag number and a column for the status.
Now that we see the results, let us turn off the trace flag that we had enabled for our test.
DBCC TRACEOFF(3608)
Here are related blog posts on the topic of the trace flags.
- SQL SERVER – What are my Trace Flags Enabled on SQL Server?
- SQL SERVER – TempDB and Trace Flag 1117 and 1118 – Not Required
- How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201
- SQL SERVER – Trace Flags – DBCC TRACEON
If you have any further questions, you may reach out to me on Twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
