Archive for the ‘General’ Category

SQL Server startup parameters (Part 4)

January 28th, 2008 No comments

There are 2 SQL Server startup parameter, that don’t start the instance, but give an information about the product.

-? – Lists available startup parameters when sqlsrvr.exe executed for a command prompt. List only documented parameters.

-v – Lists instance version, platform and build information, you have to use -s <instance name> parameter when ran against a named instance of the SQL Server. This is a non-documented startup parameter.

Categories: General Tags:

SQL Server startup parameters (Part 3)

January 28th, 2008 No comments

Beginning with SQL Server 2000, Microsoft flagship database product supports multiple instance per server. When starting SQL Server as a service (from Windows Service Manager or from a command line with “net start” notation), instance information is embedded into the SQL Server service definition. When starting SQL Server from the command line in order to start non-default, named instance you need to use -s startup parameter.

-s <instance name> – Starts SQL Server named instance. Sqlservr.exe have to be started from the instance’s Binn directory: (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\ BINN). You can also use -c startup switch alongside -s option to shorten instance startup time.

Categories: General Tags:

SQL Server startup parameters (Part 2)

January 21st, 2008 No comments

There are 3 documented startup parameters that always present in any SQL Server instance. These parameter define location for the SQL Server instance’s master database (data and log files) and also location for the SQL Server instance ERRORLOG file.

-d – SQL Server instance master database data file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\Data\ master.mdf).

-l – SQL Server instance master database log file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\Data\ mastlog.ldf).

-e – SQL Server instance log file location (default: C:\Program Files\Microsoft SQL Server\<INSTANCE ID>\MSSQL\LOG\ERRORLOG).

These startup flags (and also flags from my previous post) become very useful when you need to move system databases (master, model, temdb for the SQL Server 2000, 2005, 2008 and resource database for the SQL Server 2005 and 2008) to a new location. Complete procedure described in these 2 Microsoft resources:

There are 2 SQL Server trace flags (not documented in the SQL Server Books on-line) that are mentioned in the links provided above and also helpful when you relocate SQL Server instance databases:

trace flag 3608 – bypass instance startup recovery for all database except master (KB 224071)

trace flag 1802 – SQL Server 2005 fix to allow re-attache network share based database files (KB 922804)

Categories: General, Trace Flags Tags:

SQL Server startup parameters (Part 1)

January 11th, 2008 No 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 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:

Why is it

November 9th, 2007 No comments

This blog is going to cover several Microsoft SQL Server performance related topics. Over the years I assembled an extensive list of trace flags available for the Microsoft flagship database. So large portion of the blog articles will be devoted to trace flags and their impact on performance and behavior of the SQL Server engine.

A word of advise related to the SQL Server trace flags. They are a very useful instrument for troubleshooting of the database engine, they do help to uncover performance and stability related issues. But… Be very careful when going to use some of the trace flags on your production server. While Microsoft documents some of the trace flags at web site and several flags described in SQL Server Books On Line, test procedures for the database engine with trace flags is less extensive than without them. So before going to a production deployment with some trace flag enabled, test, test, test. And then test some more.

I also going to ‘trace’ here changes and updates to the Microsoft SQL Server product line.

And finally, my name is Konstantin Korobkov and I’ve been working with SQL Server for more than 10 years and with other database like Oracle, Sybase and Teradata for almost 15 years. Feel free to send me questions and suggestions at

Categories: General Tags: