Systems Engineering and RDBMS

Start up parameters in SQL Server

Posted by decipherinfosys on June 20, 2007

Very rarely does one need to add to the start up parameter list in SQL Server but when you do need to (when working with MSFT PSS or trying to add trace flags), you should know what those parameters do and where to add them. In this blog post, we will go over the start up parameters that exist in SQL Server by default for a given installation and the additional ones that can be added to the list. First, let’s take a look at where and how you can define these start up parameters. In SQL Server 2000, you can access them through the Enterprise Manager by right clicking the server name (instance) and selecting properties. When the screen that is shown below appears, you have the tab at the bottom that states “Start up Parameters”.

startup_blog_pic_1.jpg

Once you click on that tab, you will get the following window – addition or removal of the start-up parameters can be done here.

startup_blog_pic_2.jpg

In SQL Server 2005, you will make use of the SQL Server configuration manager. Once you open it up, right click on the instance for which you want to configure the start-up parameters and select properties:

startup_blog_pic_3.jpg

startup_blog_pic_4.jpg

You can add/remove your start-up configuration parameters here. A semi-colon is used to separate out each start-up parameter. Another way (non-GUI) to add/remove these parameters is to use the “sqlservr.exe parameter” command from the command line.

Now that we have seen where and how to add/remove the start-up parameters, let’s go over these parameters:

By default, you will see three start-up parameters which can be used to change the location of the master database data and log file and the error logs location:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;

-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;

-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

Some other start-up parameters that you might find useful (Standard Disclaimer: Please involve MSFT PSS if you are un-sure of these parameters or play around with these in an environment that is non-critical):

-f: This can be used to start up SQL Server with minimal configuration. This is useful in scenarios where a bad configuration setting has prevented your instance from starting.

-T trace#: This is one of the most useful options – this can be used to specify the trace flags that SQL Server should use when starting up like the 1204, 1211 trace flags etc.

-x: This option disables the CPU time and cache hit ratio statistics.

-m: This options starts up SQL Server in a single user mode. This is typically helpful when you need to repair the system databases.

-g memory_to_reserve: This option can be used to specify a number in MB that the engine will leave available within the SQL Server process but outside of the memory pool – this is used for automation objects that are used in T-SQL, or distributed queries using linked servers, extended procedure’s .dll files. The default is 256MB and you can increase it if you need to by using this parameter.

There are overall 9 different start-up options but the ones mentioned above are the ones that we have used at different occasions. You can read more about the remaining options in SQL Server BOL under the topic: “Using the SQL Server Service Startup Options”. One thing to note is that in case you are using the “net start” command to start up the SQL Server service, you need to use slashes (/) instead of the hyphens (-) for these start-up parameters. While some of these options are typically used only at the time of troubleshooting/repairing (like the -m or -f options), you may want to use certain options like -g, or -T all the time and you can configure those using the steps given above. When troubleshooting, we would recommend using these with the sqlservr.exe from the command line and for options that you want to always have in place, you can use the GUI – that will also make the changes to the registry keys so that they are taken into account every time.

One Response to “Start up parameters in SQL Server”

  1. […] Start up parameters in SQL Server […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: