SQL Server Startup Trace flags

Why in the world would you go there?

Easy enough – then again no – especially not if you are contemplating to change trace flags for more than one server in your environment using what all experienced DBAs would attempt to do a TSQL approach. With a little bit of research you’ll find where the 3 default startup parameters are residing inside the registry.

DECLARE @@RegistryValues TABLE (registry_key nvarchar(max), value_name nvarchar (max), value_data nvarchar (max))

INSERT INTO @@RegistryValues SELECT registry_key, value_name, convert(nvarchar(max),value_data) from sys.dm_server_registry where value_name like ‘SQLArg%’

SELECT * FROM @@RegistryValues

For a default install of SQL 2016 the result would look like this

registry_key

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\Parameters

value_name

SQLArg0-2

value_data

-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-eC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

telling SQL Server Service where to find it’s vital information (i.e. master database data and log file and the often forgotten -filled with vital information- ERRORLOG. Indeed errors due get logged in there – but there is so much more information in there – if you’ve never looked inside – grab a copy and see what you can find.

A couple of lines from the top you should see the line referencing the Registry startup parameters and eventual Traceflags at the end.

Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Back to the problem at hand – knowing that one can set startup traceflags using the recommended SQL Server Configuration manager approach – unwilling to do this for a large number of SQL servers in a one by one approach – and not being able to do this in PowerShell for SQL Server yet (probably due to lack of SQLPS experience) a TSQL approach was/is highly desirable.

DECLARE
@@Parameter nvarchar(max)=‘-T1222’, — new value_data for a startup traceflag
@Argument_Number tinyint, — SQLArgument Number -default arguments are 0-2
@Argument nvarchar(max), — SQLArg
@Reg_Hive nvarchar(max), — The Registry hive we are working with HKLM…
@CMD nvarchar(max) — The syntax to be created and used for the registry modification
IF exists (select * from sys.dm_server_registry where value_name like ‘SQLArg%’ and convert(nvarchar(max),value_data)=@@Parameter)
BEGIN
PRINT ‘Parameter already exists in the registry’
END

ELSE

BEGIN
–Adding the SQL Startup parmater to the registry
SELECT @Reg_Hive=substring(registry_key,len(‘HKLM\’)+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(nvarchar(1),@Argument_Number)
SET @CMD=‘master..xp_regwrite
”HKEY_LOCAL_MACHINE”,
 ”’+@Reg_Hive+”’,
 ”’+@Argument+”’,
”REG_SZ”,
 ”’+@@Parameter+””
EXEC (@CMD)

Everything looked fine in preliminary testing – then SQL Server ‘argues’

–Msg 22002, Level 16, State 1, Line 12 RegCreateKeyEx() returned error 5, ‘Access is denied.’

Taking a quick look at the permissions in the registry (see above) MSSQLSERVER needs to be granted Full Control of this key before a TSQL script approach to change/add Startup Parameters will work.

This said it might be time to switch over to the SysAdmin skill side and see what can be done via Group Policy to temporarily elevate permissions on the respective key.

Stay tuned  🙂

 

 

 

 

Advertisements

One thought on “SQL Server Startup Trace flags

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s