Start SQL Server in Single User Mode

How to start a named instance of SQL Server in single-user mode (Command Prompt)

1. Stop the MS SQL service (services.msc)
2. Drop to CMD
3. Go to the MS SQL server BIN folder
4. Run "sqlservr.exe -c - m -s {instancename}"

How to start the default instance of SQL Server in single-user mode (Command Prompt)

1. Stop the MS SQL service (services.msc)
2. Drop to CMD
3. Go to the MS SQL server BIN folder
4. Run "sqlservr.exe -c -m"
   

Startup Options for MS SQL server
When you install Microsoft® SQL Server™, SQL Server Setup writes a set of default startup options in the Microsoft Windows® 2000 registry. You can use these startup options to specify an alternate master database file, master database log file, or error log file.

-dmaster_file_ path
The fully qualified path for the master database file (typically, C:\Program Files\Microsoft SQL Server\MSSQL\Data\Master.mdf). If you do not provide this option, the existing registry parameters are used.

-eerror_log_ path
The fully qualified path for the error log file (typically, C:\Program Files\Microsoft SQL Server\MSSQL\Log\Errorlog). If you do not provide this option, the existing registry parameters are used.

-lmaster_log_path
The fully qualified path for the master database log file (typically C:\Program Files\Microsoft SQL Server\MSSQL\Data\Mastlog.ldf).
You can override the default startup options temporarily and start an instance of SQL Server by using the following additional startup options.

-c
Shortens startup time by starting an instance of SQL Server independently of the Service Control Manager, so that SQL Server does not run as a Microsoft Windows NT® 4.0 or Windows 2000 service.

-f
Starts an instance of SQL Server with minimal configuration. Useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Enables the sp_configure allow updates option. By default, allow updates is disabled.

-g
Specifies the amount of virtual address space (in megabytes) SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. This is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 megabytes (MB).
Use of this option may help tune memory allocation, but only when physical memory exceeds 2 gigabytes (GB) for the SQL Server 2000 Personal Edition or SQL Server 2000 Standard Edition, or 3 GB for SQL Server 2000 Enterprise Edition. Configurations with less physical memory will not benefit from using this option. Use of this option may be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which an instance of SQL Server may not start or may encounter run-time errors.

Use the default for the -g parameter unless you see the following warning in the SQL Server error log:
WARNING: Clearing procedure cache to free contiguous memory
This message may indicate that SQL Server is trying to free parts of the SQL Server memory pool in order to find space for items such as extended stored procedure .dll files or automation objects. In this case, consider increasing the amount of memory reserved by the -g switch. Using a value lower than the default will increase the amount of memory available to the buffer pool and thread stacks; this may, in turn, provide some performance benefit to memory-intensive workloads in systems that do not use many extended stored procedures, distributed queries, or automation objects.

-m
Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. By default, allow updates is disabled.

-n
Does not use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, it is recommended that you use the -e startup option too; otherwise, SQL Server events are not logged.

-s
Allows you to start a named instance of SQL Server 2000. Without the -s parameter set, the default instance will attempt to start. You must switch to the appropriate BINN directory for the instance at a command prompt before starting sqlservr.exe. For example, if Instance1 were to use \mssql$Instance1 for its binaries, the user must be in the \mssql$Instance1\binn directory to start sqlservr.exe-sinstance1.

/Ttrace#
Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior.

-x
Disables the keeping of CPU time and cache-hit ratio statistics. Allows maximum performance.

Important
When specifying a trace flag with the /T option, use an uppercase "T" to pass the trace flag number. A lowercase "t" is accepted by SQL Server, but this sets other internal trace flags that are required only by SQL Server support engineers. (Parameters specified in the Control Panel startup window are not read.)