By: Manvendra Singh | Comments | Related: > SQL Server on Linux
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.
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.
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)
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.
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.
Now we will run DBCC tracestatus to check whether this trace is turned off. We can see 3205 trace flag has disappeared from list.
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.
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.
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.
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
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.
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
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.
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.
Step 6:
We can check the current status of these trace flags before restarting SQL Server and see they are still enabled.
Now let's restart the SQL Server service as suggested above.
#Restart SQL Server service systemctl restart mssql-server
Now we will check these trace flags by running DBCC tracestatus and this time we can see they are no longer there.
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.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
