SQL Server Startup Parameters

^ click above ^

07.03.03

By Randy Dyess

Every now and then we all have to reread something we may already know because frankly if you are like me, you can't keep everything in your head. I tend to go back over tips and tricks and even database concepts when I have time so I will maybe remember enough about it so when a problem arises I at least know where to go look and find the answer. It is with, that I want to write about my latest re-education attempt.

A few years ago, I was working with a SQL Server database and something went wrong. Yea I know, how weird nothing ever goes wrong with SQL Server or any other product out there but in this case something did go wrong and corrupted my master database. At least I can say that this particular problem was not SQL Server or a bug in one of my procedures, it was actually a problem with a disk controller. Go figure, according to my network and hardware folks, nothing is ever wrong with the network or hardware. The problem is always internal to the application. To make a short story short, a drive controller was going out and corrupted the master database causing me to do what I hope was a once in a lifetime job of restoring a master database. While this particular article is not about rebuilding or restoring a master database, it is about something that a lot of DBAs overlook, startup parameters.

SQL Server allows you to control a few of its configurable options during startup with the use of startup parameters. These parameters are usually useful for troubleshooting purposes - usually in case of extreme emergencies, but they can also be useful for some long-term configuration changes as well. Before we go any further, I want to add a little warning here - do not learn about startup parameters on any SQL Server installation that you are not willing to completely rebuild. It is possible to set a parameter and cause serious problems that may affect your installation, like setting the location of the master database to a false one.

You have two easy ways to set the startup parameters in a SQL Server installation. The first is from the command line by using the sqlservr.exe parameter command. This way is useful for those emergencies in which you have to start SQL Server in single user mode or with minimal configurations. The second method is by using Enterprise Manager and the startup parameters dialog box. This method is great for long-term parameter requirements like the locations of the master database data and log files and the location of the error logs.

To change a parameter through the Enterprise Manager:

Open Enterprise Manager and right click on a registered installation.

Click Properties.

Click the Startup Parameters button.

Add your new parameters or remove an existing one. Be careful not to remove the default parameters that give the locations of the master database, the master database log, and the error logs unless you provide an alternate location.

If you take a look at the startup parameters dialog box, will see there are a few default parameters that are part of every SQL Server installation: -dmaster_data_file_path, -lmaster_log_file_path, and -eerror_log_path. You can use these default parameters to change the locations of the master database file, the master database log file, or SQL Server error logs. I would not do this unless you have a problem with the default location's drive or if the master database or error logs outgrow their current directories and you need to move them to another drive. If you are simply moving the master database data file or log file to another location, then you can change the parameter from Enterprise Manger before you stop the instance. If you are moving the file because of a problem, stop the instance and use the sqlservr.exe command to restart the instance pointing to the new data file or log file location.

SQL Server does have a few optional startup parameters that you can set to help troubleshoot your installation or make some minor performance optimizations.

-c Shortens SQL Server's startup time by starting the instance independent of the Service Control Manager. Starting SQL Server independent of the Service Control Manager will keep if from running as a Windows service. I have not had the need to use this parameter so I can only guess that it could be useful for troubleshooting startup issues.

-f This parameter starts an instance of SQL Server with the minimal configurations and enables the sp_configure allow updates option. This parameter is very useful if a configuration option is set that disables SQL Server. You can use this parameter to start SQL Server and change the inappropriate configuration setting back to the original.

-g This parameter will specify the amount of virtual address space that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. What does that mean? This memory allocation outside of the SQL Server memory pool is used for loading times such as extended stored procedures, dll files, OLE DB providers referenced by queries, and automation objects reference in Transact-SQL statements. The default size of this memory is 128 MB, which is usually fine for most SQL Server installations. Keep in mind that SQL Server installations under 2 GB (SQL Server 2000 Standard Edition) or 3 GB (Enterprise Edition) will only use 128 MB no matter what size you set with the -g parameter. You can however, use this parameter to optimize the memory on installations above the 2 GB and 3 GB cutoffs. Microsoft recommends that you do not use this parameter unless you see the following error in your error log.

WARNING: Clearing procedure cache to free contiguous memory

However, I have used this and seen others use this parameter for errors such as:

OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of memory.

-m This parameter may be one of the most used, especially if you are studying for the SQL Server tests. The -m parameter will start SQL Server in the single user mode and enable the sp_configure allow updates configuration option. Starting SQL Server in the single user mode does not issue a CHECKPOINT command. This parameter allows you work on your system databases if you encounter a problem with one of them. If you ever have to rebuild your master database you will be familiar with this parameter.

-n This parameter turns off SQL Server logging its errors to the Windows application log. It is recommended that if you use this parameter you should also use the -e starup parameter, which you should be doing anyway. This is another parameter that I have not used and personally, I would not recommend turning off the Windows application logging.

-s This parameter is used to start a named instance of SQL Server. Without the use of this parameter, SQL Server will try to start the default instance, which may or may not be present or the one you want to start.

/Ttrace# Used to start a Trace Flag when you start SQL Server. This parameter is useful for general troubleshooting purposes.

-x This parameter will disable the keeping of the CPU time and CACHE-HIT ratio statistics. While this parameter allows maximum performance, I do not use it as the benefits of the CACHE-HIT ratio outweigh the small performance gain this parameter buys you.

A quick guide to the startup parameters found in SQL Server that you could use for those extreme emergencies. Most of the time, you will not have to use these parameters but they are nice to know for that rare instance that you have system database problems or when you need to start a Trace Flag.

About the Author:
Randy has been working with SQL Server for over 6 years as both a development and production DBA. His clients have ranged from small companies to large firms dealing with 25 Terabytes of data with single databases approaching 2.5 Terabytes in size. He is currently the author of Transact-SQL Language Reference Guide and numerous SQL Server newsletter articles. You can find out more about his book and works at http://www.transactsql.com.

Read this newsletter at: http://www.sqlpronews.com/2003/0703.html

WebmasterFree Downloads