Managing Your SQL Servers

2/15/2012

Contents

Managing SQL Server Startup

The Database Engine has two modes of operation. It can run as a service or as a command-line application (SQLServr.exe). You normally run SQL Server as a service. Use the command-line application when you need to troubleshoot problems or modify configuration settings in single-user mode.

Enabling or Preventing Automatic SQL Server Startup

In Chapter 2, you’ll see how SQL Server Configuration Manager is used to manage the SQL Server (MSSQLSERVER) service, related services for other Database Engine instances, and other SQL Server–related services. Any of these services can be configured for automatic startup or can be prevented from starting automatically. To enable or prevent automatic startup of a service, follow these steps:

Start SQL Server Configuration Manager by using one of the following techniques:
- Log in to the database server through a local or remote login, and then start SQL Server Configuration Manager by clicking Start; pointing to All Programs, Microsoft SQL Server 2012, Configuration Tools; and then selecting SQL Server Configuration Manager.
- In SQL Server Management Studio, open the Registered Servers view by pressing Ctrl+Alt+G. Use the Registered Servers toolbar to select the top-level group, and then expand the group nodes by double-clicking them. Right-click the server entry, and then select SQL Server Configuration Manager.
Select the SQL Server Services node. Right-click the SQL Server service that you want to start automatically, and then select Properties. You can now do the following:
- Enable automatic startup. On the Service tab, set the Start Mode to Automatic. If the server state is Stopped, click Start on the Log On tab to start the service.
- Prevent automatic startup. On the Service tab, set the Start Mode to Manual.
Click OK.

You can also use Computer Management to configure services. To configure automatic startup of a service by using Computer Management, follow these steps:

Click Start, point to All Programs, Administrative Tools, and then select Computer Management.
By default, you are connected to the local computer. To connect to a remote computer, right-click the Computer Management node and select Connect To Another Computer. In the Select Computer dialog box, select Another Computer, and then type the name of the computer. The name can be specified as a host name, such as EngDBSrv12, or as an FQDN, such as EngDBSrv12.cpandl.com.
Expand Services And Applications, and then select Services.
Right-click the SQL Server service that you want to start automatically, and then select Properties.
Now you can do the following:
- Enable automatic startup. On the General tab, set the Startup Type to Automatic. If the Service Status reads Stopped, click Start.
- Prevent automatic startup. On the General tab, set the Startup Type to Manual.
Click OK.

Setting Database Engine Startup Parameters

Startup parameters control how the SQL Server Database Engine starts and which options are set when it does. You can configure startup options by using SQL Server Configuration Manager or Computer Management. SQL Server Configuration Manager is the recommended tool for this task because it provides the current default settings and allows you to make modifications easily.

TIP

You can pass startup parameters to the command-line utility SQLServr.exe as well. Passing the –c option to this utility starts SQL Server without using a service. You must run SQLServr.exe from the Binn directory that corresponds to the instance of the SQL Server Database Engine that you want to start. For the default instance, the utility is located in MSSQL11.MSSQLSERVER\MSSQL\Binn. For named instances, the utility is located in MSSQL11.InstanceName\MSSQL\Binn.

Adding Startup Parameters

You can add startup parameters by completing the following steps:

Start SQL Server Configuration Manager by using one of the following techniques:
- Log in to the database server through a local or remote login, and then start SQL Server Configuration Manager. On the Microsoft SQL Server 2012 menu, the related option is found under Configuration Tools.
- In SQL Server Management Studio, open the Registered Servers view by pressing Ctrl+Alt+G. Use the Registered Servers toolbar to select the top-level group, and then expand the group nodes by double-clicking them. Right-click the server entry, and then select SQL Server Configuration Manager.
Select the SQL Server Services node. Right-click the SQL Server service that you want to modify, and then select Properties.
On the Advanced tab, click in the Startup Parameters box, and then press End to go to the end of the currently entered parameters. The –d, –e, and –l parameters are set by default. Be careful not to modify these or other existing parameters accidentally.
Each parameter is separated by a semicolon. Type a semicolon and then a hyphen followed by the letter and value of the parameter you are adding, such as ;–g512.
Repeat steps 3 and 4 as necessary to specify additional parameters and values.
Click Apply to save the changes. The parameters are applied the next time the SQL Server instance is started. To apply the parameters right away, you must stop and then start the service by clicking Restart on the Log On tab.

Removing Startup Parameters

You can remove startup parameters by completing the following steps:

Start SQL Server Configuration Manager by using one of the following techniques:
- Log in to the database server through a local or remote login, and then start SQL Server Configuration Manager by clicking Start, pointing to All Programs, Microsoft SQL Server 2012, Configuration Tools, and then selecting SQL Server Configuration Manager.
- In SQL Server Management Studio, open the Registered Servers view by pressing Ctrl+Alt+G. Use the Registered Servers toolbar to select the top-level group, and then expand the group nodes by double-clicking them. Right-click the server entry, and then select SQL Server Configuration Manager.
Select the SQL Server Services node. Right-click the SQL Server service that you want to modify, and then select Properties.
On the Advanced tab, click in the Startup Parameters box. Each parameter is specified with a hyphen, parameter letter, and parameter value. A semicolon is used to separate parameter values, as shown in the following example:
```
-g512;
```
Remove the parameter by deleting its entry.
The change is applied the next time the SQL Server instance is started. To apply the change right away, you must stop and then start the service by clicking Restart on the Log On tab.

Common Startup Parameters

Table 1-1 shows the startup parameters in SQL Server and how they are used. The first three parameters (–d, –e, and –l) are the defaults for SQL Server. The remaining parameters allow you to configure additional settings.

Table 1-1 Startup Parameters for SQL Server

Parameter	Description
–d<path>	Sets the full path for the master database. If this parameter is omitted, the registry values are used. Example: –dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Master.mdf
–e<path>	Sets the full path for the error log. If this parameter is omitted, the registry values are used. Example: –eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG
–l<path>	Sets the full path for the master database transaction log. If this parameter is omitted, the registry values are used. Example: –lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Mastlog.ldf
–B	Sets a breakpoint in response to an error; used with the –y option when debugging.
–c	Prevents SQL Server from running as a service. This setting makes startup faster when you are running SQL Server from the command line.
-E	Increases the number of extents that are allocated for each file in a file group. Useful for data warehouse applications with a limited number of users.
–f	Starts SQL Server with minimal configuration. This setting is useful if a configuration value has prevented SQL Server from starting.
–g number	Specifies the amount of virtual address space memory in megabytes to reserve for SQL Server. This memory is outside the SQL Server memory pool and is used by the extended procedure dynamic-link libraries (DLLs), the OLE DB providers referenced in distributed queries, and the automation object referenced in T-SQL. The default value is 256. Example: –g256
–K	Forces regeneration of the service master key if it exists.
–k number	Sets the checkpoint speed in megabytes per second. Use a decimal value. Example: –k25
–m	Starts SQL Server in single-user mode. Only a single user can connect, and the checkpoint process is not started. Enables the sp_configure allow updates option, which is disabled by default.
–n	Tells SQL Server not to log errors in the application event log. Use with –e.
–s instance	Starts the named instance of SQL Server. You must be in the relevant Binn directory for the instance. Example: –sdevapps
–T<tnum>	Sets a trace flag. Trace flags set nonstandard behavior and are often used in debugging or diagnosing performance issues. Example: –T237
–t<tnum>	Sets an internal trace flag for SQL Server. Used only by SQL Server support engineers. Example: –t8837
–x	Disables statistics tracking for CPU time and cache-hit ratio. Allows maximum performance.
–y number	Sets an error number that causes SQL Server to dump the stack. Example: –y1803

Managing Services from the Command Line

You can start, stop, and pause SQL Server as you would any other service. On a local system, you can type the necessary command at a standard command prompt. You also can connect to a system remotely and then issue the necessary command. To manage the default database server instance, use these commands:

NET START MSSQLSERVER. Starts SQL Server as a service.
NET STOP MSSQLSERVER. Stops SQL Server when running as a service.
NET PAUSE MSSQLSERVER. Pauses SQL Server when running as a service.
NET CONTINUE MSSQLSERVER. Resumes SQL Server when it is running as a service.

To manage named instances of SQL Server, use the following commands:

NET START MSSQL$instancename. Starts SQL Server as a service; instancename is the actual name of the database server instance.
NET STOP MSSQL$instancename. Stops SQL Server when it is running as a service; instancename is the actual name of the database server instance.
NET PAUSE MSSQL$instancename. Pauses SQL Server when it is running as a service; instancename is the actual name of the database server instance.
NET CONTINUE MSSQL$instancename. Resumes SQL Server when it is running as a service; instancename is the actual name of the database server instance.

You can add startup options to the end of NET START MSSQLSERVER or NET START MSSQL$instancename commands. Use a slash (/) instead of a hyphen (–), as shown in these examples:

net start MSSQLSERVER /f /m
net start MSSQL$CUSTDATAWAREHOUS /f /m

Real World

Instead of referencing MSSQLSERVER or MSSQL$instancename, you also can refer to the service by its display name. For the default instance, you use "SQL Server (MSSQLSERVER)" with NET START, NET STOP, NET PAUSE, and NET CONTINUE. For a named instance, you use net start "SQL Server (InstanceName)", where InstanceName is the name of the instance, such as net start "SQL Server (CUSTDATAWAREHOUS)". In both usages, the quotation marks are required as part of the command text.

Managing the SQL Server Command-Line Executable

The SQL Server command-line executable (SQLServr.exe) provides an alternative to the SQL Server service. You must run SQLServr.exe from the Binn directory that corresponds to the instance of the SQL Server Database Engine that you want to start. For the default instance, the utility is located in MSSQL11.MSSQLSERVER\MSSQL\Binn. For named instances, the utility is located in MSSQL11.InstanceName\MSSQL\Binn.

When SQL Server is installed on a local system, start SQL Server by changing to the directory where the instance of SQL Server you want to start is located and then typing sqlservr at the command line. On a remote system, connect to the system remotely, change to the appropriate directory, and then issue the startup command. Either way, SQL Server reads the default startup parameters from the registry and starts execution.

You also can enter startup parameters and switches that override the default settings. (The available parameters are summarized in Table 1-1.) You still can connect SQL Server Management Studio and SQL Server Configuration Manager to the server. However, when you do, these programs show an icon indicating that the SQL Server service is stopped because you aren’t running SQL Server via the related service. You also will be unable to pause, stop, or resume the instance of SQL Server as a Windows service.

When you are running SQL Server from the command line, SQL Server runs in the security context of the user, not the security context of the account assigned to the SQL Server service. You should not minimize the command console in which SQL Server is running because doing so causes Windows to remove nearly all the resources from SQL Server.

In addition, when you are running SQL Server from the command line, you can make configuration changes that might be necessary for diagnosing and resolving problems, and you also can perform tasks that you can accomplish only when SQL Server is running in single-user mode. However, you should be careful when creating databases, changing data file locations, or making other similar types of changes. If you are logged in as an administrator and create a new database or change the location of a data file, SQL Server might not be able to access the database or data file when it runs later under the default account for the SQL Server service.

You must shut down the instance of SQL Server before logging off Windows. To stop an instance of SQL Server started from the command line, complete the following steps: