Skip to content

start-stop-pause-resume-restart-sql-server-services.md

Latest commit

 

History

History
351 lines (232 loc) · 17.2 KB

start-stop-pause-resume-restart-sql-server-services.md

File metadata and controls

351 lines (232 loc) · 17.2 KB
title Start, stop, pause, resume, and restart SQL Server services
description Find out how to start, stop, pause, resume, or restart various SQL Server services. See how to use Transact-SQL, PowerShell, and other tools for these actions.
author rwestMSFT
ms.author randolphwest
ms.date 12/07/2022
ms.service sql
ms.subservice configuration
ms.topic conceptual
helpviewer_keywords
SQL Server Configuration Manager, start and stop services
stopping SQL Server Agent
parameters [SQL Server], startup options
SQL Server, startup options
Database Engine [SQL Server], starting and stopping services
pausing SQL Server
PowerShell [SQL Server], starting and stopping services
single-user mode [SQL Server], starting in
SQL Server Management Studio [SQL Server], starting or stopping services
stopping SQL Server Browser service
starting SQL Server Agent
default instances [SQL Server], starting and stopping
SQL Server Agent, starting and stopping
command prompt [SQL Server], starting and stopping SQL Server services
continuing SQL Server
starting SQL Server Database Engine
net stop commands [SQL Server]
command prompt [SQL Server], SQL Browser service
Configuration Manager, start and stop services
resuming SQL Server
startup options [SQL Server]
named instances [SQL Server], starting and stopping
net start commands [SQL Server]
SQL Server, starting and stopping
stopping SQL Server
starting SQL Server Browser service
SQL Server Database Engine setting startup options
administering SQL Server, starting and stopping services
Management Studio [SQL Server], starting or stopping services

Start, stop, pause, resume, and restart SQL Server services

[!INCLUDE SQL Server]

This article describes how to start, stop, pause, resume, or restart the [!INCLUDEssNoVersion] [!INCLUDEssDE], the SQL Server Agent, or the SQL Server Browser service on Windows by using SQL Server Configuration Manager, SQL Server Management Studio (SSMS), net commands from a command prompt, Transact-SQL, or PowerShell.

For [!INCLUDEssNoVersion] on Linux, see Start, stop, and restart SQL Server services on Linux.

Identify the service

[!INCLUDEssNoVersion] components are executable programs that run as Windows services. Windows services can run without displaying any activity on the computer screen and without user interaction on the command line.

[!INCLUDEssDE] service

The [!INCLUDEssDE] service can be the default instance (limit one per computer) or can be one of many named instances on the computer. Use SQL Server Configuration Manager to find out which instances of the [!INCLUDEssDE] are installed on the computer. The default instance (if you install it) is listed as SQL Server (MSSQLSERVER). Named instances (if you install them) are listed as SQL Server (<instance_name>). By default, [!INCLUDEssNoVersion] is installed as SQL Server (SQLEXPRESS).

SQL Server Agent service

The SQL Server Agent service executes scheduled administrative tasks, which are called jobs and alerts. For more information, see SQL Server Agent. SQL Server Agent isn't available in every edition of [!INCLUDEssNoVersion]. For a list of features that are supported by the editions of [!INCLUDEssNoVersion], see Features Supported by the Editions of SQL Server 2019.

SQL Server Browser service

The SQL Server Browser service listens for incoming requests for [!INCLUDEssNoVersion] resources and provides clients information about [!INCLUDEssNoVersion] instances installed on the computer. A single instance of the SQL Server Browser service is used by all instances of [!INCLUDEssNoVersion] installed on the computer.

Additional Information

  • If you pause the [!INCLUDEssDE] service, users who are already connected can continue to work until their connections are broken, but new users can't connect to the [!INCLUDEssDE]. Use Pause when you want to wait for users to complete their work before you stop the service, which lets them complete transactions that are in progress. Resume allows the [!INCLUDEssDE] to accept new connections again. The SQL Server Agent service can't be paused or resumed.

  • The SQL Server Configuration Manager and SSMS display the current status of services by using the following icons.

SQL Server Configuration Manager SQL Server Management Studio (SSMS)
Started A green arrow on the icon next to the service name A white arrow on a green circle icon next to the service name
Stopped A red square on the icon next to the service name A white square on a red circle icon next to the service name
Paused Two vertical blue lines on the icon next to the service name Two vertical white lines on a blue circle icon next to the service name
Restarting A red square indicates that the service stopped, and then a green arrow indicates that the service started successfully None

  • You won't have access to all possible options when using SQL Server Configuration Manager or SSMS, depending on the state of the service. For example, if the service is already started, Start is unavailable.

  • When running on a cluster, the [!INCLUDEssNoVersion] [!INCLUDEssDE] service is best managed by using Cluster Administrator.

Permissions

By default, only members of the local administrator group can start, stop, pause, resume, or restart a service. To grant non-administrators the ability to manage services, see How to grant users rights to manage services in Windows Server 2003. (The process is similar on other versions of Windows Server.)

Stopping the [!INCLUDEssDE] by using the Transact-SQL SHUTDOWN command requires membership in the sysadmin or serveradmin fixed server roles, and isn't transferable.

SQL Server Configuration Manager

Start SQL Server Configuration Manager

On the Start menu, select All Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.

The SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program, and it may not appear as an application in some versions of Windows. For more information, see SQL Server Configuration Manager.

Start, stop, pause, resume, or restart an instance of the [!INCLUDEssNoVersion] [!INCLUDEssDE]

  1. Start SQL Server Configuration Manager, using the instructions above.

  2. If the User Account Control dialog box appears, select Yes.

  3. In SQL Server Configuration Manager, in the left pane, select SQL Server Services.

  4. In the results pane, right-click SQL Server (MSSQLServer) or a named instance, and then select Start, Stop, Pause, Resume, or Restart.

  5. Select OK to close the SQL Server Configuration Manager.

Note

To start an instance of the [!INCLUDEssDE] with startup options, see Configure Server Startup Options (SQL Server Configuration Manager).

Important

Starting with [!INCLUDE sssql22-md], when you set the Start Mode for a [!INCLUDE ssnoversion-md] service to Automatic in Configuration Manager, the service will start in Automatic (Delayed Start) mode instead, even though the Start Mode shows as Automatic.

Start, stop, pause, resume, or restart the SQL Server Browser or an instance of the SQL Server Agent

  1. Start SQL Server Configuration Manager, using the instructions above.

  2. If the User Account Control dialog box appears, select Yes.

  3. In SQL Server Configuration Manager, in the left pane, select SQL Server Services.

  4. In the results pane, right-click SQL Server Browser, or SQL Server Agent (MSSQLServer) or SQL Server Agent (<instance_name>) for a named instance, and then select Start, Stop, Pause, Resume, or Restart.

  5. Select OK to close the SQL Server Configuration Manager.

Note

SQL Server Agent cannot be paused.

SQL Server Management Studio

Start, stop, pause, resume, or restart an instance of the [!INCLUDEssDE]

  1. In Object Explorer, connect to the instance of the [!INCLUDEssDE], right-click the instance of the [!INCLUDEssDE] you want to start, and then select Start, Stop, Pause, Resume, or Restart.

    Or, in Registered Servers, right-click the instance of the [!INCLUDEssDE] you want to start, point to Service Control, and then select Start, Stop, Pause, Resume, or Restart.

  2. If the User Account Control dialog box appears, select Yes.

  3. When prompted if you want to act, select Yes.

Start, stop, or restart an instance of the SQL Server Agent

  1. In Object Explorer, connect to the instance of the [!INCLUDEssDE], right-click SQL Server Agent, and then select Start, Stop, or Restart.

  2. If the User Account Control dialog box appears, select Yes.

  3. When prompted if you want to act, select Yes.

Command Prompt window using net commands

The [!INCLUDEssNoVersion] services can be started, stopped, or paused by using Windows net commands.

Start the default instance of the [!INCLUDEssDE]

  • From a command prompt, enter one of the following commands:

    net start "SQL Server (MSSQLSERVER)"

    -or-

    net start MSSQLSERVER

Start a named instance of the [!INCLUDEssDE]

  • From a command prompt, enter one of the following commands. Replace <instancename> with the name of the instance you want to manage.

    net start "SQL Server (instancename)"

    -or-

    net start MSSQL$instancename

Start the [!INCLUDEssDE] with startup options

  • Add startup options to the end of the net start "SQL Server (MSSQLSERVER)" statement, separated by a space. When started using net start, startup options use a slash (/) instead of a hyphen (-).

    net start "SQL Server (MSSQLSERVER)" /f /m

    -or-

    net start MSSQLSERVER /f /m

    [!NOTE]
    For more information about startup options, see Database Engine Service Startup Options.

Start the SQL Server Agent on the default instance of [!INCLUDEssNoVersion]

  • From a command prompt, enter one of the following commands:

    net start "SQL Server Agent (MSSQLSERVER)"

    -or-

    net start SQLSERVERAGENT

Start the SQL Server Agent on a named instance of [!INCLUDEssNoVersion]

  • From a command prompt, enter one of the following commands. Replace instancename with the name of the instance you want to manage.

    net start "SQL Server Agent (instancename)"

    -or-

    net start SQLAgent$instancename

For information about how to run SQL Server Agent in verbose mode for troubleshooting, see sqlagent90 Application.

Start the SQL Server Browser

  • From a command prompt, enter one of the following commands:

    net start "SQL Server Browser"

    -or-

    net start SQLBrowser

Pause or stop services from the command prompt window

  • To pause or stop services, modify the commands in the following ways.

  • To pause a service, replace net start with net pause.

  • To stop a service, replace net start with use net stop.

Transact-SQL

The [!INCLUDEssDE] can be stopped by using the SHUTDOWN statement.

Stop the [!includessde] using transact-sql

  • To wait for currently running Transact-SQL statements and stored procedures to finish, and then stop the [!INCLUDEssDE], execute the following statement.

    SHUTDOWN;

  • To stop the [!INCLUDEssDE] immediately, execute the following statement.

    SHUTDOWN WITH NOWAIT;

For more information about the SHUTDOWN statement, see SHUTDOWN (Transact-SQL).

PowerShell

Start and stop [!INCLUDEssDE] services

  1. In a Command Prompt window, start [!INCLUDEssNoVersion] PowerShell by executing the following command.

    sqlps

  2. At a [!INCLUDEssNoVersion] PowerShell command prompt, by executing the following command. Replace computername with the name of your computer.

    # Get a reference to the ManagedComputer class.
CD SQLSERVER:\SQL\computername
$Wmi = (get-item .).ManagedComputer

  3. Identify the service that you want to stop or start. Pick one of the following lines. Replace instancename with the name of the named instance.

    • To get a reference to the default instance of the [!INCLUDEssDE].

      $DfltInstance = $Wmi.Services['MSSQLSERVER']

    • To get a reference to a named instance of the [!INCLUDEssDE].

      $DfltInstance = $Wmi.Services['MSSQL$instancename']

    • To get a reference to the SQL Server Agent service on the default instance of the [!INCLUDEssDE].

      $DfltInstance = $Wmi.Services['SQLSERVERAGENT']

    • To get a reference to the SQL Server Agent service on a named instance of the [!INCLUDEssDE].

      $DfltInstance = $Wmi.Services['SQLAGENT$instancename']

    • To get a reference to the SQL Server Browser service.

      $DfltInstance = $Wmi.Services['SQLBROWSER']

  4. Complete the example to start and then stop the selected service.

    # Display the state of the service.
$DfltInstance
# Start the service.
$DfltInstance.Start();
# Wait until the service has time to start.
# Refresh the cache.
$DfltInstance.Refresh();
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop();
# Wait until the service has time to stop.
# Refresh the cache.
$DfltInstance.Refresh();
# Display the state of the service.
$DfltInstance

Use the ServiceController class

You can use the ServiceController class to programmatically control the [!INCLUDEssNoVersion] service, or any other Windows service. For an example using C#, see ServiceController Class.

Next steps