How do you stop a SQL Server database?

By: Daniel Farina | Updated: 2020-02-06 | Comments (2) | Related: More > Database Administration

Free MSSQLTips Webinar: How to Avoid Worst Practices as a SQL Server DBA

Learn from 20 years of experience about how to turn SQL Server Worst Practices such as database corruption, security, scaling, monitoring, cloud cost management, and SQL Agent monitoring into Best Practices to properly manage your database environment.

Problem

There are often times that you need to stop and start SQL Server services, so in this tip we will look at various methods of doing this.

Solution

Handling SQL Server services can be a challenging task for people other than experienced SQL Server DBAs. If you are a DBA you may think that I am exaggerating, but I have seen along my career cases of sysadmins and even SQL Server developers that don’t know how to start and stop SQL Server services. In most of the cases, they don’t know which of the SQL Server related services is the one they need to start.

Five Different Methods To Start, Stop, And Restart SQL Server Services

HomeTools & technologiesFive Different Methods To Start, Stop, And Restart SQL Server Services

When we install the SQL Server, it also installs the following services:

1. SQL Server database engine service to manage and access data in SQL Server.
2. SQL Server Integration Service for the ETL process – we install it when including Integration services during setup.
3. SQL Server Reporting Service to manage the SQL Server reports.
4. The SQL Server Agent Service. Note that it is not available in the SQL Server Express edition.

In this article, we are going to learn the following methods to manage SQL Server services:

1. Start, stop, and restart SQL Server services using the SQL Server configuration manager.
2. Start, stop, and restart SQL Server services using Services MMC (Microsoft Management Console).
3. Start, stop, and restart SQL Server services using the PowerShell script.
4. Start, stop, and restart SQL Server services in Ubuntu Linux.
5. Start, stop, and restart SQL Server services using the Windows server failover cluster manager.

Let us examine all these methods.

Three different ways to change the status of SQL Services

Below are examples of how to Start, Pause, Stop, and Restart the SQL Services using SQL Server Configuration Manager, the Command Prompt, and SQL Server Management Studio. While these tasks can be completed with SQL PowerShell and T-SQL statements, I chose the prior three methods because I felt they were easier to use when it came to applying these commands to named instances.

(Warning: While it is possible to start and stop the services using the services.msc, it is against best practices and may lead to database corruption.)

Local server administrators group membership is required by default to Start, Pause, Stop, or Restart services using SQL Server Configuration Manager, SQL Server Management Studio, or the Command Prompt using net commands. In situations where admin access to the OS has been lost, or granting server administrator privileges is not preferred, please consult you Systems Administrator and consider the alternatives presented in the following article: How to Start or Stop SQL Services without OS Admin Rights.

Using SQL Server Configuration Manager

Best Practice: Use the SQL Server Configuration Manager when making any changes to the SQL Services. It ensures that all of the changes made to the SQL Services get propagated to all of the necessary registry entries and applies any necessary permissions when changing the account the service is running under. SQL Server Configuration Manager also provides a validation check if changes are made to the service account used to start SQL Server Service. This prevents an invalid account being assigned to the SQL Server Service which will block the service from starting.

To use the SQL Server Configuration Manager, select the appropriate file for your SQL Server version and Run as Administrator:

SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc

Example of SQL Server Configuration Manager with a default instance (MSSQLSERVER) and a named instance (Development).

Using the Command Prompt with net Commands

Caution: While it is possible to change the service’s status through the command prompt, it is best practices to use the SQL Server Configuration Manager.

Run the Command Prompt as the local administrator

For Named instances

Use the same Command Prompt commands, but replace MSSQLSERVER with the instance name (i.e. net start “SQL Server (Development)” ).

Using SQL Server Management Studio

Example of SQL Server Management Studio connected to the default instance and the “Development” named instance.

There are some limitations to using SSMS for managing the state of the SQL Services, so again I highly recommend using SQL Server Configuration Manager when making changes.

Starting

SQL Server Configuration Manager

• Right-click the instance you want to start and select “Start”.
• Note: If you manually start the SQL Server service, you will need to independently start the SQL Server Agent. Hint: Since SQL Server Agent is dependent on SQL Server Service, starting SQL Server Agent will start both services.

Command Prompt with net Commands

• To start the SQL Server Service enter the following command :
• net start “SQL Server (MSSQLSERVER)”
• To start the SQL Agent Service enter the following command:
• net start “SQL Server Agent (MSSQLSERVER)”

SQL Server Management Studio
NOTE: The only way to start a SQL Server instance within SSMS is if SSMS was connected to the instance prior to it being stopped.

• Right-click the instance you want to start and select “Start”
• Click yes on the pop-up message to confirm that you want to Start the SQL Server Service
• After the SQL Server Service is started, right-click the SQL Server Agent and select “Start”
• Click yes on the pop-up message to confirm that you want to Start the SQL Server Agent Service.

Pausing

A special note about Pausing the SQL Server Service
Pausing the SQL Server Service allows current connections to stay active, but it prevents new connections. This is beneficial when you want connected users to be able to finish their tasks prior to stopping the SQL Server Service.
SQL Server Configuration Manager

• Right-click the instance you want to pause and select “Pause”.

Command Prompt with net Commands

• net pause “SQL Server (MSSQLSERVER)”

SQL Server Management Studio

• Right-click on the instance and select “Pause”.
• Click yes on the pop-up message to confirm that you want to Pause the SQL Server Service.

Resuming

SQL Server Configuration Manager

• Right-click on the instance and select “Resume”.

Command Prompt with net Commands

• net continue “SQL Server (MSSQLSERVER)”

SQL Server Management Studio

• To Resume SQL Server Service, right-click the instance and select “Resume”.

Stopping

SQL Server Configuration Manager

• Right-click the instance you want to stop and select “Stop”.
• Note: Stopping the SQL Server service will also stop the corresponding SQL Server Agent.

Command Prompt with net Commands

• net stop “SQL Server (MSSQLSERVER)”
• You will be notified that SQL Server Agent will also be stopped and prompted to continue. To continue enter: y
• To stop just the SQL Agent Service enter the following command:
• net stop “SQL Server Agent (MSSQLSERVER)”

SQL Server Management Studio

• Right-click on the instance and select “Stop”.
• Click yes on the pop-up message to confirm that you want to Stop the SQL Server Service.
• Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.
• Note: SQL Server Agent Service can be stopped independently from SQL Server Service by right-clicking SQL Server Agent and selecting “Stop”.
• Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.

Restarting

SQL Server Configuration Manager

• Right-click the instance you want to restart and select “Restart”.
• Restarting the SQL Server service will also restart the SQL Server Agent.

Command Prompt with net Commands
(Stopping SQL Server Service using the Command Prompt will stop the SQL Agent Service. For the purposes of restarting SQL, both the SQL Server Service and SQL Agent Service will have to be individually started.)
For the Default Instance

• net stop mssqlserver – enter ‘y’ to acknowledge that SQL Agent Service will be shut down.
• net start sqlserveragent (This will start both the SQL Server Service and the SQL Agent Service.)
• (To start just the SQL Server Service use: net start mssqlserver)

For Named Instances

• net stop MSSQL$Named_Instance_Here
• Net start SQLAgent$Named_Instance_Here
• (To start just the SQL Server Service use: net start MSSQL$Named_Instance_Here)

SQL Server Management Studio

• Right-click on the instance and select “Restart”.
• Click yes on the pop-up message to confirm that you want to restart the SQL Server Agent Service.
• Click Yes to acknowledge that SQL Server Agent Service will be restarted as well.