SQL SERVER – Checking Traceflag Status with TRACESTATUS

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.

SQL SERVER - Checking Traceflag Status with TRACESTATUS TRACESTATUS-800x189

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.

SQL SERVER - Checking Traceflag Status with TRACESTATUS TRACESTATUS1

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.

If you have any further questions, you may reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Delayed Durability and Flushing Log Files
Next Post
SQL SERVER – Database Giving Error While Setting Single_User Mode

Related Posts

Leave a Reply

Menu