articles >> general dba >> SQL Server Startup Parameters

SQL Server Startup Parameters

By : Randy Dyess Jul 20, 2003

Page 2 / 3

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 them so when a problem arises, I at least know where to go look and find the answer. It is with this in mind 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 nothing weird 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, SQL Server 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 often useful for troubleshooting purposes -- usually in the cases 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:

1. Open Enterprise Manager and right click on a registered installation.
   
    
2. Click Properties.
   
    
3. Click the Startup Parameters button.
   
    
4. 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

-eerror_log_path

You can use these default parameters to change the locations of the master database file, the master database log file, or the 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.