Speak guys!
In this post today I will give a very quick tip, which is how to identify the instance initialization parameters using T-SQL, very useful scenario when you have access to connects in the instance, but do not have access to the server operating system.
Using SQL Server Configuring Manager
For those who don't know, the SQL Server service has some boot parameters, which allow you to change the behavior of SQL Server. Some of them are already set by default, such as -d (master data path), -l (master log path), and -e (errorlog default path).
When you have access to the server's operating system, you normally open SQL Server Configuration Manager, select the instance you want to view the parameters for and select the “Properties” option by right-clicking, as shown in the image below:
Using SQL Server sys.dm_server_registry 2008 R2 SP1
But what about when access is not possible? In this case, a very practical alternative is to access the Windows registry through SQL Server. SQL Server stores this startup information in the “HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \MSSQL14.SQL2017\ MSSQLServer \ Parameters ”, where MSSQL14.SQL2017 = MSSQL
To facilitate retrieval of this information, we can use DMV dm_server_registry, available from SQL Server 2008 R2 SP1, by filtering by registry_key:
| 1 2 3 | SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%Parameters' |
Result:
Using XP Registry Access xp_instance_regread
However, those who work with multiple clients and multiple versions of SQL Server may want to standardize on a single script for versions prior to SQL Server 2008 R2 SP1, where the sys.dm_server_registry DMV was not yet available.
If you are in this scenario or even your SQL Server is prior to 2008 R2 SP1, you can use the Extended Procedures (XP's) to access the registry (xp_instance_regread and xp_regread) to return the value of an individual parameter or create a loop to make that:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | DECLARE @Contador INT = 0, @Total INT = 10, @Chave VARCHAR(10) DECLARE @SQLArgs TABLE ( [Value] VARCHAR(50), [Data] VARCHAR(500), ArgNum AS CONVERT(INTEGER, REPLACE([Value], 'SQLArg', '')) ) WHILE(@Contador <= @Total) BEGIN SET @Chave = 'SQLArg' + CAST(@Contador AS VARCHAR(2)) INSERT INTO @SQLArgs EXEC sys.xp_instance_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters', @Chave SET @Contador += 1 END SELECT * FROM @SQLArgs |
Result:
Using xp_regread Registry Access XP
You can also use xp_regread, but you will have to enter the logical instance name (in this case MSSQL10_50.SQL2008), while xp_instance_regread already returns the logical name of the instance where your session is connected:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | DECLARE @Contador INT = 0, @Total INT = 10, @Chave VARCHAR(10) DECLARE @SQLArgs TABLE ( [Value] VARCHAR(50), [Data] VARCHAR(500), ArgNum AS CONVERT(INTEGER, REPLACE([Value], 'SQLArg', '')) ) WHILE(@Contador <= @Total) BEGIN SET @Chave = 'SQLArg' + CAST(@Contador AS VARCHAR(2)) INSERT INTO @SQLArgs EXEC sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQLServer\Parameters', @Chave SET @Contador += 1 END SELECT * FROM @SQLArgs |
Result:
Using XP Registry Access xp_instance_regenumvalues
Analyzing the code of the 2 solutions using XP's above, we can see that they have a big problem: Use of WHILE without a correct size defined. If it has less than 11 parameters, WHILE will be running more times than necessary. If there is more, it will stop listing some parameters. To solve this, we can use the undocumented Extended Procedure, xp_instance_regenumvalues, to help us in this need:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DECLARE @ChaveMestre VARCHAR(50) = 'HKEY_LOCAL_MACHINE', @Registro VARCHAR(100) = 'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters' DECLARE @SQLArgs TABLE ( [Value] VARCHAR(50), [Data] VARCHAR(500), ArgNum AS CONVERT(INTEGER, REPLACE([Value], 'SQLArg', '')) ) INSERT INTO @SQLArgs EXECUTE master.sys.xp_instance_regenumvalues @ChaveMestre, @Registro SELECT * FROM @SQLArgs |
Result:
Well guys, I hope you enjoyed this tip. For those who didn't know about the sys.dm_server_registry DMV or these Extended Procedures that I demonstrated in this article, you now know it.
A big hug and see you in the next post.
