• Article

sqlservr Application

The sqlservr application starts, stops, pauses, and continues an instance of Microsoft SQL Server from a command prompt.

Syntax

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f] 
     [-eerror_log_path] [-lmaster_log_path] [-m]
     [-n] [-Ttrace#] [-v] [-x] [-gnumber] [-h]

Arguments

  • -sinstance_name
    Specifies the instance of SQL Server to connect to. If no named instance is specified, sqlservr starts the default instance of SQL Server.

    Important

    When starting an instance of SQL Server, you must use the sqlservr application in the appropriate directory for that instance. For the default instance, run sqlservr from the \MSSQL\Binn directory. For a named instance, run sqlservr from the \MSSQL$instance_name\Binn directory.

  • -c
    Indicates that an instance of SQL Server is started independently of the Windows Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start.

    Note

    When you use this option, you cannot stop SQL Server by using SQL Server Service Manager or the net stop command, and if you log off the computer, SQL Server is stopped.)

  • -dmaster_path
    Indicates the fully qualified path for the master database file. There are no spaces between -d and master_path. If you do not provide this option, the existing registry parameters are used.

  • -f
    Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.

  • -eerror_log_path
    Indicates the fully qualified path for the error log file. If not specified, the default location is <Drive>:\Program Files\Microsoft SQL Server\MSSQL\Log\Errorlog for the default instance and <Drive>:\Program Files\Microsoft SQL Server\MSSQL$instance_name\Log\Errorlog for a named instance. There are no spaces between -e and error_log_path.

  • -lmaster_log_path
    Indicates the fully qualified path for the master database transaction log file. There are no spaces between -l and master_log_path.

  • -m
    Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode. The CHECKPOINT mechanism, which guarantees that completed transactions are regularly written from the disk cache to the database device, is not started. (Typically, this option is used if you experience problems with system databases that require repair.) Enables the sp_configure allow updates option. By default, allow updates is disabled.

  • -n
    Allows you to start a named instance of SQL Server. Without the -s parameter set, the default instance attempts 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 -s instance1. If you start an instance of SQL Server with the -n option, it is advisable to use the -e option too, or SQL Server events are not logged.

  • -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. For more information, see Trace Flags (Transact-SQL).

    Important

    When specifying a trace flag, use -T to pass the trace flag number. A lowercase t (-t) is accepted by SQL Server; however, -t sets other internal trace flags required by SQL Server support engineers.

  • -v
    Displays the server version number.

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

  • -gmemory_to_reserve
    Specifies an integer number of megabytes (MB) of memory that SQL Server leaves available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory outside of the memory pool 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 MB.

    Use of this option may help tune memory allocation, but only when physical memory exceeds the configured limit set by the operating system on virtual memory available to applications. 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 any of the following warnings in the SQL Server error log:

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

    These messages 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 increases 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.

  • -h
    Reserves virtual memory address space for Hot-Add memory metadata when AWE is enabled with 32-bit SQL Server. Required for Hot-Add memory with 32 bit AWE, but consumes about .5 gigabytes (GB) of virtual address space and makes memory tuning more difficult. Not required for 64-bit SQL Server.

Remarks

In most cases, the sqlservr.exe program is only used for troubleshooting or major maintenance. When SQL Server is started from the command prompt with sqlservr.exe, SQL Server does not start as a service, so you cannot stop SQL Server using net commands. Users can connect to SQL Server, but SQL Server tools show the status of the service, so SQL Server Configuration Manager correctly indicates that the service is stopped. SQL Server Management Studio can connect to the server, but it also indicates that the service is stopped.