Did you start your SQL Server Engine correctly?
Often we run into situations where there is a need to add a trace flag or a startup parameter other than the default startup parameters that SQL Server uses. What you may notice sometimes is that even after adding these parameters/flags, the desired behavior from those parameters/flags does not occur.
We will use the following parameter and trace flag to demonstrate the reasons but this applies to any combination of trace flags and parameters.
"-g <memory in MB>" – This is used to expand the Mem-To-Leave region of SQL Server memory on x86 systems when you observe that your SQL Server is running out of memory in this region (256MB by default). You can read more about the purpose of –g here.
"-T1118" – This is used to reduce tempdb contention. More details at this earlier blog post.
All startup parameters are stored in Windows Registry and read by SQL Server every time it is started. For this purpose, they need to be added via SQL Server Configuration Manager (SSCM) so that they can be persisted and used every time SQL Server is started.
NOTE: Depending on your situation (example: tempdb contention), even if the trace flags are added correctly, you may still continue to experience the same problem. This blog post does not apply to such situations.
Upon further investigation, we have observed that depending on how the parameter is added from SSCM, the registry keys may not be created correctly resulting in SQL Server startup code skipping these parameters.
To add startup parameters correctly from SSCM, follow these guidelines:
- Add any new parameters after the first 3 default parameters –d, -e, -l. –d, -e and -l need to be the first 3 (and in that order) for SQL Server to start correctly.
- Specify the ";" delimiter between each parameter. SSCM uses ";" as the delimiter between parameters.
- Do NOT use a space after the delimiter and the parameter identifier (which is the "-" character).
Not following these guidelines will result in these being created incorrectly in registry. As a result, SQL Server startup code will not be able to parse these parameters correctly.
If ";" delimiter is not used, the parameter will be combined with the previous parameter and ignored during parsing. If there is a white-space after the delimiter (before the "-" character), the parameter is added to the registry with this white space and is again ignored by command line parser.
Here are few examples on the effects of incorrectly adding startup parameters or trace flags. I have also listed how to recognize the problem from the error log and correct them.
Problem: Leading white space before –T . In this example, there is a single white space between the delimiter ";" (for mastlog.ldf) and (-T).
After restart, SQL Server Error Log shows the following (note that –T1118 is indented by one position in relation to other parameters).
2010-01-11 17:34:43.81 Server Registry startup parameters:
2010-01-11 17:34:43.81 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-01-11 17:34:43.81 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-01-11 17:34:43.81 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-01-11 17:34:43.81 Server -T1118
Solution: Remove the single white-space before –T1118 and restart SQL Server.
Problem: No ';" delimiter specified before –g. In this example, there is no delimiter between –T1118 and –g512. This will be treated as a single parameter (as both of them will be in same registry key) as seen in the errorlog below (Note: Both parameters are printed on the same line which is how you recognize this problem). In this situation, -g will be ignored.
2010-01-11 17:34:43.81 Server Registry startup parameters:
2010-01-11 17:34:43.81 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-01-11 17:34:43.81 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-01-11 17:34:43.81 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-01-11 17:34:43.81 Server -T1118 -g512
Solution: Add the ";" delimiter after –T1118. After restart, error log will show each parameter in a new line.
Problem: This is a combination of above scenarios (single white space and missing delimiter) and both parameters will be ignored.
2010-01-11 17:34:43.81 Server Registry startup parameters:
2010-01-11 17:34:43.81 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-01-11 17:34:43.81 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-01-11 17:34:43.81 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-01-11 17:34:43.81 Server -T1118 -g512
Solution: Remove the single white-space before –T1118, add ";" delimiter after –T1118 and restart SQL Server.
Fortunately, as you can see, SQL Server errorlog is your friend here and prints the parameters exactly as they were created in the registry. When in doubt, start with the error log first to make sure the startup parameters are setup correctly.
To summarize:
- Add new parameters after the default parameters.
- Use the ";" delimiter between each parameter.
- Do not use a space after the delimiter ";" and before the "-" character.
- When in doubt, check the current SQL Server error log and verify that each parameter shows up in a new line and starts at the same indentation.
Ajay Jagannathan | Senior Escalation Engineer | Microsoft SQL Server Escalation Services