Chapter 1 - Starting, Pausing, and Stopping SQL Server

Before you log in to Microsoft SQL Server, you need to know how to start, pause, and stop SQL Server. After you are logged in, you can perform various tasks such as administering the server or querying a database.

Because SQL Server is integrated with the Microsoft Windows NT® Service Control Manager, it can be started and stopped as a Windows NT service (MSSQLServer), either locally or remotely. If you are running Microsoft Windows® 95, SQL Server Service Manager can stop, start, pause, and check the state of local services. Unlike Windows NT, it cannot remotely administer services.

Note If you have to restart your computer, Service Control Manager will be brought up automatically, with the default Service displayed in the Service Control Manager. When you explicitly quit Service Control Manager, the current Service will be saved and will be opened the next time Service Control Manager is opened. However, when you shut down or log off the computer, the current Service will not be saved as the default Service.

SQL Server can be started automatically as a service each time Windows NT starts. This option can be specified during installation by using SQL Server Setup, and after installation using SQL Server Enterprise Manager or the Services application in Control Panel.

You can log off from the Windows NT network without shutting down SQL Server.

When you start SQL Server, you are starting the SQL Server service (MSSQLServer); SQL Server Agent runs as a service called SQLServerAgent. If the MSSQLServer and SQLServerAgent services are not configured to start automatically, you must start them manually. The SQL Server Service Manager also can be used to start, pause, or stop Microsoft Search Service and MSDTC Service. After you start the MSSQLServer service, users can establish new connections to the server. After you start the SQLServerAgent service, you can use SQL Server's scheduling features.

You can pause SQL Server before stopping the server. Pausing SQL Server prevents new users from logging in and gives you time to send a message to current users asking them to complete their work and log out before you stop the server.

If you stop SQL Server without pausing it, all server processes are terminated immediately. Stopping SQL Server prevents new connections and disconnects current users.

See Also 

In This Volume 

Registering Servers

Configuring the SQLServerAgent Service

Starting SQL Server

You can start Microsoft SQL Server automatically or manually. Each method for starting SQL Server starts SQL Server as a Microsoft Windows NT service, except manually running sqlservr from a command prompt.

See Also 

In This Volume 

SQL Server Startup Options

In Other Volumes 

"SQL Server Service Manager" in Microsoft SQL Server Introduction 

Starting SQL Server Automatically

During installation, you can configure Microsoft SQL Server to automatically start each time you start Microsoft Windows NT. You can also use the Services application in Control Panel to configure SQL Server as an automatically started service. After SQL Server is installed, you can enable or disable this option using SQL Server Enterprise Manager. For more information, see your Windows NT documentation. The best way to start a server running Windows 95/98 automatically is to select Auto-start service when OS starts in SQL Server Service Manager.

See Also 

In This Volume 

SQL Server Startup Options

Starting SQL Server Manually

You can start Microsoft SQL Server manually using these methods.

Method	Description
SQL Server Enterprise Manager	Start, pause, continue, and stop a local or remote MSSQLServer or SQLServerAgent service in the same window in which you administer other servers and databases.
SQL Server Service Manager	Start, pause, continue, and stop a local or remote MSSQLServer installation or SQLServerAgent service.
Services application in Control Panel	Start, pause, continue, and stop the MSSQLServer or SQLServerAgent service on the local server.
Command prompt	Start MSSQLServer or SQLServerAgent from a command prompt by typing: net start mssqlserver or sqlservr, or net start SQLServerAgent.

Before you choose a specific startup method, consider the following issues:

• If you start SQL Server as a Microsoft Windows NT service, you can log off the Windows NT network without having to shut down SQL Server. 

  If you start SQL Server using sqlservr from a command prompt (independent of the Service Control Manager):

  • All system messages appear in the window used to start SQL Server. 

  • You cannot pause, stop, or resume SQL Server as a service using SQL Server Enterprise Manager, SQL Server Service Manager, the Services application in Control Panel, or any net commands (for example, net start, net pause, net stop, and net continue). 

  • Before logging off from Windows NT, you must shut down SQL Server. 

  • If you start SQL Server from a command prompt, any command-prompt options that you type take precedence over the default command-prompt options written to the Windows NT Registry by SQL Server Setup. 

  • If you start SQL Server from a command prompt, SQL Server Service Manager and SQL Server Enterprise Manager show the service as stopped. 

See Also 

In This Volume 

SQL Server Startup Options

Starting SQL Server in Single-User Mode

Under certain circumstances, you may need to start Microsoft SQL Server in single-user mode. This is the startup option -m. For example, you may want to change server configuration options or recover a damaged master or other system database; both require starting SQL Server in single-user mode.

When you start SQL Server in single-user mode:

• Only one user can connect to the server. 

• The CHECKPOINT process is not executed. By default, it is executed automatically at startup. 

• The sp_configure system stored procedure allow updates option is enabled. By default, the allow updates option is disabled. 

See Also 

In This Volume 

SQL Server Startup Options

allow updates Option

In Other Volumes 

"CHECKPOINT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Starting SQL Server with Minimal Configuration

If you need to correct configuration problems that prevent the server from starting, you can start Microsoft SQL Server using the minimal configuration startup option. This is the startup option -f. Starting SQL Server with minimal configuration places the server in single-user mode automatically.

When you start SQL Server in minimal configuration mode:

• Temporary changes to memory usage are made. The options user connections, open databases, locks, open objects, language in cache, max async, and asynchronous I/O are all set to the minimum values shown by sp_configure. The procedure cache is set to 50 percent, with minimal total procedure and buffer cache. 

• SQL Server starts in single-user mode. Only a single user can connect, and the CHECKPOINT process is not executed. 

• Remote access and read-ahead are disabled. 

• Startup stored procedures are not run. 

• The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. 

After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

Important Stop the SQLServerAgent service before connecting to SQL Server in minimal configuration mode. Otherwise, the SQLServerAgent service uses the connection and blocks your connection to SQL Server.

See Also 

In This Volume 

SQL Server Startup Options

Setting Configuration Options

In Other Volumes 

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Logging In to SQL Server

You can log in to Microsoft SQL Server from any of the graphical administration tools, or from a command prompt, using osql. When you log in to SQL Server using a graphical administration tool such as SQL Server Enterprise Manager or SQL Server Query Analyzer, you are prompted to supply the server name, a login ID, and a password if necessary. How you log in to SQL Server depends on whether SQL Server is using Windows NT Authentication or mixed mode (SQL Server and Windows NT Authentication). If SQL Server is using Windows NT Authentication, you do not have to provide a login ID each time you access a registered SQL Server. Instead, SQL Server logs you in automatically using your Microsoft Windows NT account.

However, if SQL Server is using mixed mode, you must log in to SQL Server to administer it. The first time you log in, you must use sa (the default system administrator login ID) as your login identification. Initially, the sa login does not have a password.

Note If you selected a case-sensitive sort order when you installed SQL Server, your login ID is also case-sensitive.

See Also 

In This Volume 

Managing Security Accounts

Managing Servers

Authentication

In Other Volumes 

"sp_password" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Other Startup Features

Microsoft SQL Server has several other startup features. You can use the default startup options provided by SQL Server Setup, or you can override these options temporarily. For example, if you experience problems with system databases that should be repaired, you can use the -m option, which starts SQL Server in single-user mode.

You can also run SQL Server on a network. Microsoft Windows NT starts its built-in SQL Server Service automatically when Windows NT starts. However, if you want to connect to a stand-alone SQL Server from a local client such as osql, you can bypass the network and connect directly to SQL Server using a local pipe, or without a network.

SQL Server Startup Options

When you install Microsoft SQL Server, SQL Server Setup writes a set of default startup options for SQL Server in the Microsoft Windows Registry. You can use these startup options to specify an alternate master database file, master database log file, or error log file.

Default startup options	Description
-d master_file_ path	The fully qualified path for the master database file (typically, C:\Mssql7\Data\Master.mdf). If you do not provide this option, the existing Registry parameters are used.
-e error_log_ path	The fully qualified path for the error log file (typically, C:\Mssql7\Log\Errorlog). If you do not provide this option, the existing Registry parameters are used.
-l master_log_path	The fully qualified path for the master database log file (typically C:\Mssql7\Data\Mastlog.ldf).

If you need to override the default startup options temporarily, you can start SQL Server using other startup options. For example, you may want to start SQL Server in single-user mode by using the -m startup option, or start SQL Server with minimal configuration by using the -f startup option.

Other startup options	Description
-c	Shortens startup time by starting SQL Server independently of the Microsoft Windows NT Service Control Manager, so that SQL Server does not run as a Windows NT service.
-f	Starts SQL Server with minimal configuration. Useful if 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.
-m	Starts SQL Server in single-user mode. When you start 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 NT application log to log SQL Server events. If you start SQL Server with -n , it is advisable to use the -e startup option too; otherwise, SQL Server events are not logged.
-p precision_level	Specifies the maximum level of precision to be supported by decimal and numeric data types. By default, SQL Server has a maximum precision of 28. The acceptable values for precision_level are from 1 to 38; however, if no precision_level is supplied, a maximum precision of 38 is assumed.
-s registry_key	Starts SQL Server using an alternate set of startup parameters stored in the Registry under the key registry_key. This option, which can be run only from the command prompt, lets you select from multiple previously defined startup configurations. You could, for example, create an alternate Registry key to start SQL Server in single-user mode. (This applies only to the Server subkey.)
/T trace#	Indicates that 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 by SQL Server.)

See Also 

In Other Volumes 

"CHECKPOINT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Creating and Storing Alternate Startup Options

When you install Microsoft SQL Server, SQL Server Setup writes a set of default startup options for SQL Server in the Microsoft Windows NT Registry under the key:

HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
\MSSQLServer
\Parameters

You can create and store alternate sets of startup options in the Registry. For example, you can start SQL Server in single-user mode. Each startup option is stored as a separate parameter in the Parameters key of the MSSQLServer key, starting with SQLArg0, then SQLArg1, and so on. The order of the parameters is not important.

Caution Editing the Registry is not recommended because inappropriate or incorrect changes can cause serious configuration problems for your system. Only experienced users should use the Registry Editor program to edit the Registry database. For more information, see your Windows NT or Microsoft Windows 95/98 documentation.

Running SQL Server on a Network

For Microsoft SQL Server to communicate over the network, the Server (network) service must be running. By default, Microsoft Windows NT automatically starts its built-in Server service when Windows NT starts. To find out whether the Server service has been started, type at the command prompt:

net start

If the Server service has been started, it is listed in the net start output along with the other services that are running services. For example:

C:\> net start
These Windows NT services are started:

ClipBook Server
Computer Browser
EventLog
Messenger
Network DDE
Network DDE DSDM
Server
Workstation

The command completed successfully.

If the Server service has not been started, type at the command prompt:

net start server

The following message indicates that the service has been started:

The Server service was started successfully.

You can also use the Services application in Control Panel to check service status and to start and stop services. For more information, see your Windows NT documentation.

Running SQL Server Without a Network

When running Microsoft SQL Server without a network, you do not need to start the built-in MSSQLServer service. Because SQL Server Enterprise Manager, SQL Server Service Manager, and the net start and net stop commands are always functional (even without a network), the procedures for starting and stopping SQL Server are identical for a network or stand-alone operation.

When connecting to a stand-alone SQL Server from a local client such as osql, you bypass the network and connect directly to the SQL Server using a local pipe. The difference between a local pipe and a network pipe is whether you are using a network. Both local and network pipes establish a connection with SQL Server using the standard pipe (\pipe\sql\query), unless otherwise directed.

When you connect to a local SQL Server without specifying a server name, you are using a local pipe. When you connect to a local SQL Server and explicitly specify a server name, you are using either a network pipe or another network IPC mechanism such as IPX/SPX (if you have configured SQL Server to use multiple networks). Because a stand-alone SQL Server does not support network pipes, you must omit the unnecessary /S server_name argument when connecting to the SQL Server from a client. For example, to connect to a stand-alone SQL Server from osql, type:

osql /Usa /P

Pausing and Resuming SQL Server

When you pause Microsoft SQL Server, users who are connected to the server can finish tasks, but new connections are not allowed. For example, you can pause SQL Server for a few minutes and send a shutdown message to connected users before shutting it down. You can also resume a SQL Server service.

Note You cannot pause SQL Server if it was started by running sqlservr. Only SQL Server services started as Microsoft Windows NT services can be paused.

For information about pausing and resuming SQL Server from the Services application in Control Panel, see your Windows NT documentation.

Stopping SQL Server

You can stop Microsoft SQL Server locally from the server or remotely from a client or another server.

Method	Description
SQL Server Enterprise Manager	Stops a local or remote SQL Server or a SQLServerAgent service.
SQL Server Service Manager	Stops a local or remote SQL Server or a SQLServerAgent service from a single window or from the Microsoft Windows taskbar.
SHUTDOWN statement	Stops SQL Server when executed within osql or another query tool. Using the WITH NOWAIT option stops SQL Server immediately.
net stop mssqlserver	Stops SQL Server either remotely or locally if you are running Windows NT.
Control Panel	Stops SQL Server using the Services application in Control Panel.
CTRL+C	Stops SQL Server if it was started as a program from the command prompt.

When you stop SQL Server, the server performs these services before it shuts down:

• Disables logins (except for system administrators). 

• Performs a CHECKPOINT in every database. However, if you stop SQL Server using CTRL+C at the command prompt, it does not perform a CHECKPOINT in every database. Therefore, the recovery time is increased the next time the server is started. 

• Waits for all currently executing Transact-SQL statements or stored procedures to finish. 

Note To bring the system to an immediate halt, you can issue the SHUTDOWN WITH NOWAIT statement from the osql utility.

Broadcasting a Shutdown Message

Before you stop Microsoft SQL Server, you can broadcast a message to warn users of an impending shutdown. In the message, you can include the time SQL Server will be stopped so users can finish their tasks.

See Also 

In Other Volumes 

"SHUTDOWN" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SQL Server Service Manager" in Microsoft SQL Server Introduction 

"sp_lock" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_who" in  Microsoft SQL Server Transact-SQL and Utilities Reference 

More About Using the SHUTDOWN Statement

If SQL Server Enterprise Manager or SQL Server Service Manager is not available, you can stop Microsoft SQL Server by issuing a SHUTDOWN command from osql or another query tool.

The SHUTDOWN statement minimizes automatic recovery time when you restart SQL Server. The SHUTDOWN statement stops SQL Server in this manner:

1. Logins are disabled (except for the system administrator login). To see a list of all current users, use the sp_who system stored procedure. 

2. Transact-SQL statements or stored procedures that are running are allowed to finish. To see a list of all active processes and locks, use the sp_who and sp_lock system stored procedures. 

3. A CHECKPOINT is performed in every database. 

Note Issuing the SHUTDOWN WITH NOWAIT statement stops the server immediately. However, it requires more recovery time the next time the server is started because no CHECKPOINT is issued against any databases.

See Also 

In Other Volumes 

"SHUTDOWN" in Microsoft SQL Server Transact-SQL and Utilities Reference