Microsoft recently released SQL Server to run on Linux servers, providing more flexibility for database administrators. In this blog post, we will explore how to enable and disable SQL Server trace flags globally on Linux.
Enabling a SQL Server Trace Flag Globally
In SQL Server on Windows, enabling a trace flag globally can be done using either the DBCC Traceon command or the SQL Server Configuration Manager. However, on SQL Server running on Linux, we need to use the SQL Server configuration manager utility called mssql-conf.
To enable a trace flag globally on Linux, follow these steps:
- Connect to SQL Server using sqlcmd.
- Retrieve all enabled trace flag information using the command:
DBCC tracestatus(-1). - To enable a specific trace flag, use the command:
sudo /opt/mssql/bin/mssql-conf traceflag [traceflag_number] on. For example, to enable trace flag 1222 to capture deadlocks globally, use:sudo /opt/mssql/bin/mssql-conf traceflag 1222 on. - Restart the mssql service using the command:
systemctl restart mssql-server.service. - Verify if the trace flag is enabled by checking the trace flag information again.
Enabling Multiple SQL Server Trace Flags in Linux
If you need to enable multiple trace flags at once, you can do so by specifying the trace flag numbers separated by a space in the command. For example, to enable trace flags 1204 and 3205, use the command: sudo /opt/mssql/bin/mssql-conf traceflag 1204 3205 on.
Disabling a SQL Server Trace Flag Globally
If you want to disable a trace flag, follow these steps:
- Use the command:
sudo /opt/mssql/bin/mssql-conf traceflag [traceflag_number] off. For example, to disable trace flag 3205, use:sudo /opt/mssql/bin/mssql-conf traceflag 3205 off. - Restart the SQL Server service.
- Verify if the trace flag is disabled by checking the trace flag information again.
By following these steps, you can easily enable and disable SQL Server trace flags globally on Linux, allowing you to customize the behavior of your SQL Server instance to meet your specific needs.