Enable a SQL Server Trace Flag globally on Linux

https://www.mssqltips.com/sql-server-tip-category/226/sql-server-on-linux//

Microsoft have recently released SQL Server to run on Linux servers. Sometimes we need to enable a trace flag globally on SQL Server with this tip I'll demonstrate how to enable a SQL Ser Ver trace flag globally on Linux.

Solution

In SQL Server on Windows, if we want to enable a trace flag globally we can do it using one of the ways:

• DBCC Traceon (Traceflag,-1)
• SQL Server Configuration Manager

In this tip we'll see how to enable or disable trace flags in SQL Server running on Linux to use it globally.

To enable or disable a SQL Server trace flag on SQL Server on Linux, we need to use the SQL Server Configuration Manager u Tility called mssql-conf.

Mssql-conf is a configuration script this installs with SQL Server VNext CTP 1.3 for Red Hat Enterprise Linux, SUSE Linux Enterprise Server and Ubuntu.

Enable a SQL Server Trace Flag globally on Linux

First let's connect to SQL Server with sqlcmd and get all enabled trace flag information using DBCC tracestatus (-1).

To enable a trace flag on Linux We need to use the following command:

sudo/opt/mssql/bin/mssql-conf Traceflag <traceflag_no>on

Suppose we want to enable Traceflag 1222 to run globally to capture deadlocks.

sudo/opt/mssql/bin/mssql-conf Traceflag 1222 on

Now we need to restart the MSSQL service.

Systemctl Restart Mssql-server.service

Now let's check the trace flag again to see if Traceflag 1222 is enabled.

We can see here, the Traceflag 1222 is enabled globally.

Enable multiple SQL Server Trace Flags in Linux

Suppose we want to enable multiple trace flags i.e. 1204, 3205 at once. We can do the as shown in this script:

sudo/opt/mssql/bin/mssql-conf Traceflag 1204 3205 on

Now let's check the trace flags again to see if these traceflags is enabled.

Disable SQL Server Trace Flags globally in Linux

Suppose we want to disable a trace flag. We need to use the below command.

sudo/opt/mssql/bin/mssql-conf Traceflag <traceflag_no>off

If we want to disable trace flag 3205, run the command as:

sudo/opt/mssql/bin/mssql-conf Traceflag 3205 Off

Once we have restarted the SQL Server service and verify the trace flag is disabled. We cannot see the disabled trace flag 3205 This since it has been disabled.

Next Steps

• Enable and disable a traceflag as per your requirements, but don't perform these steps directly on production systems wit Hout proper testing.
• Read more about SQL Server on Linux Tips.

Enable a SQL Server Trace Flag globally on Linux