In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.

In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.

Let’s start off with how the environment is configured. The below screen shot (from SQL Server Configuration Manager) shows the startup parameters now in use:

Registry-Current Startup Parameters

Notice that this instance has only TF 1204 enabled.

Retrieving the Startup Parameters

The registry key “HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters” stores the startup parameters. Especially relevant is the “MSSQL12.SQL2014” portion, which will vary depending on the SQL Server version and your instance name. However, if you remember from the earlier post, there are instance-aware versions of the registry extended stored procedures that automatically handle this. For the instance-aware versions, use this registry key instead: “HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters”.

To get all the startup parameters, we would use the xp_instance_regenumvalues extended stored procedure. As noted in the earlier post, this produces a result set for each value. Therefore, this example loads them into a table variable and then displays this table:

This script could have omitted the two variables, but since I’m planning on using them for updating the registry, I decided to store their locations in variables and just use those instead. Additionally, since I’ll be adding to and removing from this list and I want to keep the parameters in the same order, I have added a computed column to calculate the argument number. Running this script shows all the startup parameters:

Registry-Current Startup Parameters TSQL

This matches the earlier screen shot. Running the following will show the running trace flags:

Trace Flags Current

Enabling / disabling trace flags

The next step that needs performed is to enable trace flags 1117 and 1118, and to disable trace flags 1204 and 1222. The commands DBCC TRACEON and DBCC TRACEOFF are used to accomplish this. These commands take a comma-delimited list of trace flags. To perform their action at a global level, use the optional flag of “-1”. These commands set and verify the trace flags:

Trace Flags Changed

Setting trace flags to handle the instance restarting

This will start and stop the trace flags immediately; however this has not changed their startup state when the instance next restarts. Consequently, we need to modify the registry to remove TF 1204 and to add TFs 1117 and 1118. The following statements will override SQLArg3 with the TF 1117, and add TF 1118 to SQLArg4. Remember that writing to the registry requires access for the service account to the specified keys. Optionally, the service account can be a member of the Local Administrators group. In order for SQL Server to pick up this change, the service must be restarted after these changes have been made.

Cleaning up the registry

If we remove more values than what were added, we might need to call master.sys.xp_instance_regdeletevalue. Additionally, if we are only deleting some values, there might be gaps left in the argument number which need eliminated. The following script enables and/or disables current trace flags, modifies the registry for the startup parameters, and handles the registry consolidation / cleanup of this key.

After running, you can see that the changes have in fact been changed:

Registry-Changed Startup Parameters

Registry-Changed Startup Parameters TSQL