Use T-SQL to Add SQL Server Startup Parameters

Solutions usually present themselves when a challenge arises. I recently had the challenge of needing to add trace flag 1222 for capturing deadlocks to the startup parameters on more than 1,000 SQL Server instances. All the instances were SQL Server 2008 R2 SP2 or later.

Although you can use SQL Server Configuration Manager to change startup parameters, you must establish a remote desktop session to the relevant machine to launch it. This process can be time-consuming when dealing with large numbers of scattered SQL Server instances. In addition, when using SQL Server Configuration Manager, you need to manually enter the startup parameters, which increases the chance of making mistakes.

Automate the Task

When you use SQL Server Configuration Manager to enter a startup parameter, the information about the parameter is stored in the registry as a separate entry (a string value). So, I decided to automate the task of adding the startup parameter for trace 1222 (-T1222) by directly modifying the registry with a T-SQL script. By using a script in conjunction with Central Management Server (CMS), I could rollout the registry change to 1,000 instances with a click of a button.

I created Automate_Changing_SQL_Startup_Parameters.sql, which has three sections. Figure 1 shows the first section. The highlighted @Parameters variable defines the startup parameter to be added, which in this case is -T1222.

Figure 1: First Section in Automate_Changing_SQL_Startup_Parameters.sql

Under the @Parameters variable, notice the @Argument_Number variable. In the registry, each parameter's name starts with SQLArg followed by a number, as shown in Figure 2.

Figure 2: Startup Parameter Entries in the Registry

The first parameter's name is SQLArg0, the second parameter's name is SQLArg1, and so on. The @Argument_Number variable is used as a counter for the number of existing defined startup parameters. By default, every SQL Server instance has three parameters:

Therefore, if no other startup parameter has been defined, the value of @Argument_Number would be 3, which will be used for the next startup parameter SQLArg3.

The second section, which Figure 3 shows, checks to see whether the specified startup parameter (in this case, -T1222) already exists. If it exists, the script deletes it. This is done so that the script can be rerun. Plus, it prevents the code from breaking if it encounters this situation.

Figure 3: Second Section in Automate_Changing_SQL_Startup_Parameters.sql

The registry key that holds the startup parameters can be different on different instances (e.g., different on default and named instances). For this reason, the script uses the sys.dm_server_registry dynamic management view (DMV) to get the correct registry path to the startup parameters both in the second section and the last section.

Related: T-SQL Parameters and Variables: Basics and Best Practices

As you can see in Figure 4, the last section uses xp_regwrite to add the new startup parameter.

Figure 4: Third Section in Automate_Changing_SQL_Startup_Parameters.sql

You can download Automate_Changing_SQL_Startup_Parameters.sql by clicking the Download the Code button near the top of the page. Although it's set up to add the startup parameter for trace 1222, you can easily adapt it to add a different startup parameter. However, you need to be aware of the following:

After successfully testing the script against a SQL Server instance, you can extend its functionality by using Central Management Server. For information about how to use Central Management Server, read Kalen Delaney's fantastic article "Multiserver Management with SQL Server 2008's Central Management Server."