Home > General > SQL Server startup parameters (Part 1)

SQL Server startup parameters (Part 1)

January 11th, 2008 Leave a comment Go to comments

This is a part one of a mini series on SQL Server startup parameters.

When starting SQL Server, as a service or from a command line, there are several startup parameters that define or change the behavior of that instance. Some of these parameters are well documented in the SQL Server Books Online (Books Online call them startup options) and some are not well documented. I’m going to cover both.

Startup parameters for the SQL Server instance stored in the registry:

SQL Server 2005 registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL       Server\<INSTANCE ID>\MSSQLServer\Parameters

You need to replace an <INSTANCE ID> with the instance id of the SQL Server you are going to configure. Here’s a useful link to the SQL Server 2005 (and 2008) instance naming conventions and instance ids.

SQL Server 2000 registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL       Server\<INSTANCE NAME>\MSSQLServer\Parameters

<INSTANCE NAME> here is a SQL Server 2000 instance name.

and can be set or changed via SQL Server Configuration Manager for the SQL Server 2005 and 2008 (make sure there are no spaces between parameters in the edit line when you type a new one in).

When starting SQL Server instance as a service with the SQL Server Configuration Manager or Windows Services Admin Console only parameters defined in the registry will be used, when starting a service from a command line (net start “<SQL Service name>”) both registry and command line flags will take an effect (make sure to use /flag notation instead of -flag with the net start command) and when starting form a command line, not as a service, registry as well as a command line supplied parameters are used.

or Enterprise Manager for the SQL Server 2000

It’s also possible to set startup parameters by directly editing registry keys, but be careful when doing this and take a backup of the registry data you are going to change.

Lets’s start with parameters that affect global instance configuration, and are very useful for test and troubleshooting:

-c – optimizes startup of the instance when started from a command line (skips source code branches that are related to the startup as a service). This option if oftenly used during SQL Server performance tests to achieve best results (see http://www.tpc.org SQL Server tests disclosure documents for example).

-m – starts an instance in a single-user mode (only one user can connect to the instance), CHECKPOINT process is not started and direct update to system tables is enabled. When using that option make sure that as an administrator you’ll be able to use that single connection, i.e. connect from a local server console, disable remote connections, make sure that applications and users are not able to hijack your single connection. (BTW, one of the SQL Server certification exams has a question related to this startup flag).

-f – starts SQL Server with minimal, default configuration and in a single-user mode. Used for troubleshooting a wrong configuration of the SQL Server.

Categories: General Tags:
  1. No comments yet.
  1. No trackbacks yet.