(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 47-------------------------------------------------- T-SQL Script to Add SQL Server Startup Parameters By: srinivasma_exceldbp Date: April 15, 2014, 7:02 am --------------------------------------------------------- /* T-SQL Script to Add SQL Server Startup Parameters SQL Server Configuration Manager can be used to change startup parameters, establishing a remote desktop session to the relevant machine is required 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, manually entries of the startup parameters, may result in making mistakes 1. 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. 2. 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. @Parameters -- Start up parameter to be added @Argument_Number variable -- In the registry, each parameter's name starts with SQLArg followed by a number 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. */ declare @Parameters @Argument_Number @Argument @Reg_Hive @CMD --============================================================== === --** Parameter Cleanup /* 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. */ if exists ( select * from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data) = @Parameters ) begin select @Argument @Reg_Hive 1,len(registry_key)) from sys.dm_server_registry where value_name like 'SQLArg%' and convert(varchar(max),value_data) = @Parameters set @CMD = 'master..xp_regdeletevalue ''HKEY_LOCAL_MACHINE'', '''+@Reg_Hive+''', '''+@Argument+'''' exec (@CMD) end --============================================================== === --** Add Paramter /* 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 */ select @Reg_Hive 1,len(registry_key)) , @Argument_Number max(convert(int,right(value_name,1))) + 1 from sys.dm_server_registry where value_name like 'SQLArg%' group by substring(registry_key,len('HKLM\') + 1,len(registry_key)) set @Argument = 'SQLArg' + convert(varchar(1),@Argument_Number) select @Argument,@Reg_Hive set @CMD ='master..xp_regwrite ''HKEY_LOCAL_MACHINE'', '''+@Reg_Hive+''', '''+@Argument+''', ''REG_SZ'', '''+@Parameters+'''' exec (@CMD) --============================================================== === *****************************************************