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:

  1. 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.
  2. Specify the ";" delimiter between each parameter. SSCM uses ";" as the delimiter between parameters.
  3. 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:

  1. Add new parameters after the default parameters.
  2. Use the ";" delimiter between each parameter.
  3. Do not use a space after the delimiter ";" and before the "-" character.
  4. 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

Comments (5)

  1. Tibor Karaszi says:

    I typically verifiy by checking the registry to see that is made it there correctly. I once forced a newline in SQL Config Manager instead of a semi-colon, which caused the filename for master.ldf be incorrect which of course refused the instance to start. I wish the Config Manager has some more intuitive UI for handling startup options…

  2. Rudy Komacsar says:

    I still edit the registry directly. That is almost a must in SQL 2005 since the UI is abysmal when compared to SQL 2000 !!!

  3. Jim Evans says:

    This is a great post. I recently had a scenario where I set the trace flag incorrectly:

    -l C:DB MSSQL.1MSSQLDATAmastlog.ldf -T1204

    When I went to restart SQL I got error: The log scan number (2585:280:1) passed to log scan in database 'master' is not valid.

    SQL Service would not start.  I was getting ready to go through the process to rebuild the system DB then follow with restores.  I noticed the mastlog.ldf had two files in the folder: ‘mastlog.ldf’ and ‘mastlog.ldf -T1204’.  For the heck of it I backed up the two files and then renamed ‘mastlog.ldf -T1204’ to ‘mastlog.ldf’.  I was happily able to restart SQL.  What luck to fine and try this!

  4. Jim Evans says:

    Note that I was unable to start SQL after removing the invalid trace flag until I renamed the file

    ‘mastlog.ldf -T1204’.

  5. Randy in Marin says:

    Remember to type in the trace flag and avoid cut and paste.  I used MMC to remote into a server to add a trace flag via the configuration manager.  It was ignoring my -T4199 flag that I pasted in.  (I was doing this on a few instances.)  I followed this tip and compared the lines.  I noticed the hyphen I added was longer than the others. After I retyped the hyphen, all was well.

Skip to main content