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.
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.
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:
- SQLArg0 identifies the master database data file.
- SQLArg1 identifies the Errorlog folder.
- SQLArg2 identifies the master database log file.
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.
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.
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:
- This script works only on instances running SQL Server 2008 R2 SP2 or later because the sys.dm_server_registry DMV isn't available in earlier versions of SQL Server.
- For the script to run successfully, the SQL Server service account should have full access to the registry or be a member of Local Administrators group.
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."
