Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable and Disable SQL Server Trace Flags on a Linux Server


By:   |   Read Comments   |   Related Tips: More > SQL Server on Linux


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

SQL Server trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems. This tip will be helpful if you want to learn how to enable or disable SQL Server trace flags on a Linux based SQL Server instance.

Solution

As per MSDN, there are three types of trace flags in SQL Server: query, session and global.

  • Query trace flags are active for the context of a specific query.
  • Session trace flags are active for a connection and are visible only to that connection.
  • Global trace flags are set at the server level and are visible to every connection on the server.

Some flags can only be enabled as global, and some can be enabled at either global or session scope. There are two ways to enable and disable any trace flags on Linux based SQL Server. One is by using DBCC TRACEON and DBCC TRACEOFF commands and another is by using the configuration script "mssql-conf". Microsoft has provided a configuration script "mssql-conf" for Linux based SQL Servers to make configuration changes. The "mssql-conf" script installs with the SQL Server vNext installation on Linux based servers. This utility is very useful to set parameters like trace flags, TCP ports, default data file location, default log file location, etc.

Enabling and Disabling Trace Flags using DBCC TRACEON

Before going ahead, you need to have SQL Server vNext running on a Linux based server.

Step 1:

Connect to the Linux server using PuTTY or login to the server locally. I connected to my Linux server where SQL Server vNext is running through PuTTY as shown in the below picture.

connect to Linux Server

Step 2:

The next step is to connect to the SQL Server instance using sqlcmd and check the trace flags which are currently enabled on this SQL Server instance.

#Connect to SQL Server Instance. Enter the sa password once you get password prompt.
sqlcmd -S192.168.209.130 -Usa

#Display all trace flags on this SQL Server
DBCC tracestatus(-1)

We can see all trace flags which are enabled globally as shown in the below screenshot. Whenever we pass -1, it displays the status of trace flags that are enabled globally.

Displays the status of trace flags that are enabled globally.

Step 3:

Now we will enable trace flag 3205 using the DBCC traceon command. You can see trace flag 3205 is not shown in the above screenshot. Run the below DBCC traceon command to enable this trace flag.

#Enable trace flag 3205 on SQL Server Instance
DBCC traceon(3205, -1)

DBCC Traceon(3205,-1)

Now we will run DBCC tracestatus again to check and verify whether this trace flag is enabled. Run the DBCC tracestatus command as we did in step 2. We can see trace flag 3205 is now showing and it is enabled globally on this instance.

DBCC Tracestatus(-1)

Step 4:

Now we will disable the same trace flag using the DBCC command. Run the DBCC traceoff command to disable this trace flag.

#Disable trace flag 3205.
DBCC traceoff(3205,-1)

Once you will execute above command, the trace flag will be disabled and disappear from DBCC tracestatus list.

DBCC Traceoff(3205,-1)

Now we will run DBCC tracestatus to check whether this trace is turned off. We can see 3205 trace flag has disappeared from list.

DBCC Tracestatus(-1)

This was the DBCC method to enable or disable a trace flag. Now we will do the same activity using mssql-conf utility.

Enabling and Disabling Trace Flags using mssql-conf

Step 1:

First connect to the Linux server where the SQL Server instance is running using PuTTY or login locally.

Connect using PuTTY

Step 2:

Now we will connect to SQL Server using sqlcmd and check all the enabled trace flags on this SQL Server. Run DBCC tracestatus to get the details as we did in the above section.

dbcc tracestatus

We can see trace flag 3205 is not showing in the above screenshot, so let's enable this trace flag using the mssql-conf utility.

Step 3:

Type exit and press enter to exit the sqlcmd prompt.

Now we enable trace flag 3205 using the mssql-conf utility. Run the below command to get this done.

#Enable trace flag 3205 using mssql-conf
sudo /opt/mssql/bin/mssql-conf traceflag 3205 on

Once you press enter to proceed, it will ask you to enter the root password. Enter the root password and press enter to execute this statement. One thing you will notice is that it's asking to restart the SQL Server service after applying this change whereas we did not restart the SQL Server service using the DBCC command. This is one benefit of using the DBCC commands in setting trace flags.

enable 3205 using mssql-conf

We can also enable or turn on multiple trace flags in one go using the same utility. Let me turn on trace flag 260, 1205, 2528 and 1234 in one go by running the below command.

#Enable multiple trace flag using mssql-conf
sudo /opt/mssql/bin/mssql-conf traceflag 260 1205 2528 1234 on

dbcc tracestatus

Now let us run the DBCC tracestatus to check whether these trace flags have been enabled on this server before restarting SQL Server. We cannot see any of these trace flags (260, 1205, 2528, 1234 and 3205) in the below screenshot which means we need to restart SQL Server after enabling a trace flag in this manner.

dbcc tracestatus

Step 4:

Restart the SQL Server service by running the below command and then we will run DBCC tracestatus to check the enabled trace flags.

#Restart SQL Server Service.
systemctl restart mssql-server

restart sql service

We can see the service is restarted in the above screenshot. Now go ahead and run DBCC tracestatus to check the trace flags status. Now we can see the trace flags 260, 1205, 2528, 1234 and 3205 are enabled as shown below.

dbcc tracestatus

Step 5:

Now we will disable these trace flags. We will disable all these trace flags in to one go. Run the below command to turn off these trace flags.

#Disable multiple trace flags
sudo /opt/mssql/bin/mssql-conf traceflag 260 1205 2528 3205 1234 off

Once you press enter all of these trace flags will be turned off, but we need to restart the SQL Server service to apply these changes.

disable trace flags

Step 6:

We can check the current status of these trace flags before restarting SQL Server and see they are still enabled.

dbcc tracestatus

Now let's restart the SQL Server service as suggested above.

#Restart SQL Server service
systemctl restart mssql-server

restart sql server service

Now we will check these trace flags by running DBCC tracestatus and this time we can see they are no longer there.

dbcc tracestatus

I hope I have described the difference between turning on trace flags using DBCC command vs. the mssql-conf utility.

Next Steps
  • The configuration script "mssql-conf" is very useful and you should explore more about this.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools