Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automation: Changing SQL Service Startup Parameters using T-SQL

By Shaunt Khalatian,

Solutions present themselves when a challenge arises... usually!
I had a challenge of adding trace flag 1222 for capturing deadlocks to the SQL startup parameters, on over 1000 instances of SQL. All the instances were SQL Server 2008R2 SP2 and higher. It is worth mentioning that this solution would only work on SQL server 2008 R2 SP2 and higher as the sys.dm_server_registry  DMV which is been used in the script is not available in lower versions.
The SQL service startup parameters can be changed using the SQL Server Configuration Manager. A remote desktop session to the relevant machine is required in order to launch the SQL Server Configuration Manager. This process can be time consuming when dealing with large number of scattered SQL instances, also it increase the chance or making mistake as new parameters will be entered manually.
The following screenshot display the SQL Server parameters in SQL Server Configuration Manager.

Information regarding the SQL service startup parameters for each instance is stored in registry. Therefore any changes to SQL service parameters using SQL Server Configuration Manager will be directly reflected in Registry.

As shown in the following snapshot, each startup parameter is stored as a separate entry (string value). Also worth noting that each parameter has a name starting with SQLARG0 and the number in the name increments for each additional parameter.
 

Going back to our challenge, in order to add a new startup parameter (in this instance trace 1222), -T1222 should be added as SQL startup parameter. This can be achieved by adding the parameter directly using SQL Server Configuration Manager as show below.

So the idea to automate this task can be achieved by directly modifying the Registry. And with having a Script in place it can be used in conjunction with CMS, so change can be rolled out to 1000 instance with a click of a button....MAGIC
Let's dive into the script, which has three sections. First, defining the startup parameter that should be add by setting the value for @Parameters variable (highlighted  section)
The next section will do a clean up for the specified startup parameter in case it already does exist (the purpose of this section is to make re-runable, so the code will not break if the parameter already does exist) This section checks the registry hive for the SQL instance using the sys.dm_server_registry DMV. As the Registry hive that holds the startup parameters can be different for different instances (default and named instance), using this DMV can simply the process as it always will return the correct hive path for startup parameters.

The last section of the code adds the new parameter by using xp_regwrite.

Note: For the script to run successfully SQL service account should have full access to the registry hive or be a member of local admin
After successfully testing the script against a SQL server instance, functionality can be extended by using Central Management Server(CMS). In order to find out more how to use CMS to Manage your environment, I would refer you to a fantastic article by Ryan Adams

Resources:

Automate_Changing_SQL_Startup_Parameters.sql
Total article views: 2797 | Views in the last 30 days: 41
 
Related Articles
FORUM

SQL Server Instance startup

Instance startup

FORUM

SQLServer 2005 - Startup Parameters

Startup Parameters

BLOG

Denali — Day 8: Startup Parameter

Denali – Day 8: Startup Parameter Startup Parameters means you are providing the parameter to s...

FORUM

SQL Server stopped after changing startup parameters for mirroring.

SQL Server stopped after changing startup parameters for mirroring.

Tags
automation    
changing    
sql startup parameters    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones