How to stop, start and restart SQL Server services

Problem

There are often times that you need to stop, start and restart 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 in 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.

SQL Server Services

These are the services you will find in a computer that has the SQL Server database engine installed, without Analysis Services, Integration Services or Reporting Services.

In the previous table where it says <Instance Name>, you have to replace it with the name of your instance. This name is set during SQL Server installation, but there are two standard names which are MSSQLSERVER for the default instance and SQLEXPRESS for the SQL Server Express edition. Maybe you have already figured that out, but it’s worth mentioning that you can have many services which include the <Instance Name> in their name on a single server.

Windows Service Manager

The following screen capture shows a view of the Windows Service Manager on a server with a single instance of SQL Server as the default instance (MSSQLSERVER).

Also, in the next screen capture you can see the same Windows Service Manager view, but in a system with several instances of SQL Server on the same machine.

Windows Service Dependencies

Sometimes you will find that there are services that in order to work, rely on other services. This is what is called a service dependency. The SQL Server services have little dependencies. SQL Server Agent depends on the SQL Server service; and SQL Server service depends on service “CNG Key Isolation”.  The CNG Key Isolation service provides key process isolation to private keys and associated cryptographic operations.

Starting and Stopping SQL Server Services Using Windows Services Manager

When talking about starting and stopping services, the de facto tool is the Windows Service Management Console, which you can find in the Windows Administrative Tools folder. Its usage is very straightforward, you have the list of Windows services and the only thing you have to do is to find the appropriate service for the instance you want to change and by right-clicking your mouse on the service, you will see a pop-up menu.

In the pop-up menu, you can see items named “Start”, “Stop”, “Restart” that performs a start, stop on the service being selected. Of course, you will see greyed-out items that are incompatible with the current service status (i.e. you can’t start a service already started or stop a service that is not running). In the next screen capture, you will see it more clearly.

Starting and Stopping SQL Server Services Using SQL Server Configuration Manager

SQL Server Configuration Manager is the tool provided by Microsoft to perform configuration changes in the services of SQL Server. This application allows us to start, stop and restart the SQL Server services. To do so, you need to right-click on the service you want to start/stop and select the appropriate item on the contextual menu.

Starting and Stopping SQL Server Services SQL Server Management Studio

SQL Server Management Studio, which we call SSMS for short, also allows us to start and stop SQL Server services locally and in remote systems. If you right-click over a database server or a SQL Server Agent in the Object Explorer window a contextual menu will pop up. Amongst the items in this menu, there are the “Start”, “Stop” and “Restart” items. Those items perform the specified action on the service.

If you are one of those people that want to get things in order and take advantage of the Registered Servers feature of SSMS, you can also handle SQL Server services from there. What you need to do is right-click over the server name, and in the contextual menu that pops up expand the “Service Control” item. There you have the “Start”, “Stop” and “Restart” items.

Starting and Stopping SQL Server Services Using the Command Line

If you are “old school”, you may still use the command line to perform administrative tasks. There are two commands that allow you to manage services. The first and oldest one is the NET command, which allows you to handle services on the local computer; and the newest SC that works also on remote systems.

But before we start with the commands, something to note, services can be referred to by “name” or by “display name” which are not the same. Service names tend to be shorter and without blank spaces than its “display name”.  Both NET and SC commands take “name” and “display name” with no distinction. The only thing you need to consider in case you use the “display name” is to enclose it in quotes. But there is a benefit to using the “name” of the service which is that they are well known in advance because the naming has a standard that you can see on the next table.

Sample Code

As can be seen in the above table, service name for named instances are composed by MSSQL (in case of the database engine) and SQLAGENT (for the agent service) followed by a dollar sign ($) and the instance name. As an example, suppose you have an instance named TIPS, then the SQL Server service name will be MSSQL$TIPS and the agent service name SQLAGENT$TIPS

To start|stop a service using NET command the syntax is as follows

net start|stop service_name

For example, to start the SQL Agent service of the default SQL Server instance the command is

net start SQLSERVERAGENT

To stop the SQL Agent service of the default SQL Server instance the command is

net stop SQLSERVERAGENT

On the next screen capture you can see both commands in action.

If you decide to use the SC command you will see that there are few differences with the NET command, but the advantage is that you can handle services in remote systems. Of course, this depends on your user account rights for the remote computer.

Starting and Stopping SQL Server Services Using PowerShell

PowerShell is a task automation and configuration management framework. It has the advantage of being available for Linux platforms, so you can eventually start or stop SQL Server services on both Windows and Linux environments from a Linux terminal using PowerShell. Remember that PowerShell Core is still under development, and for this reason, the code I will use is not guaranteed to work on Linux systems.

To view service status using PowerShell we can use the Get-Service cmdlet passing the name of our service in the –Name parameter. The syntax to view service status of the default SQL Server instance is as follows:

Get-Service -Name MSSQLSERVER

Additionally, if you need to view the service state of a named instance whose name is Tips the command is as follows:

Get-Service -Name MSSQL$Tips

To start or stop a service using PowerShell we can use the Set-Service cmdlet passing the following:

• –Name parameter
• –Status parameter – the desired state we want for the service. For the Status parameter we must use Running in case we want to start the service and Stopped if we want the service to stop.
• –PassThru to get an output from the command in the console.

Review Service Status

The next code section shows an example on how to view the service status of the default instance and then start the service.

Get-Service -Name MSSQLSERVER
Set-Service -Name MSSQLSERVER -Status Running -PassThru
Get-Service -Name MSSQLSERVER

On the next image you can see a screen capture of the previous code execution.

Start and Stop Service Remotely with PowerShell

If we want to start / stop a SQL Server service on a remote computer using PowerShell we can use the Invoke-Command cmdlet to pass the Get-Service/Set-Service cmdlets to the remote system as follows:

Invoke-Command -ComputerName SERVER -ScriptBlock { code }

This will execute the code in braces in the remote computer named SERVER. Let’s see a practical example:

Invoke-Command -ComputerName SQL-A -ScriptBlock { Get-Service -Name MSSQLSERVER}
Invoke-Command -ComputerName SQL-A -ScriptBlock { Set-Service -Name MSSQLSERVER -Status Running -PassThru }
Invoke-Command -ComputerName SQL-A -ScriptBlock { Get-Service -Name MSSQLSERVER}

You can see the output of executing the previous code on the next screen capture.

Starting and Stopping SQL Server Services Using dbatools

Dbatools is a free PowerShell module that can be downloaded from this URL: https://dbatools.io/ that includes administration, development, and migration commands ready to be used for SQL Server.

To view the status of SQL related services this module includes a cmdlet named Get-DbaService which can be used to get the status of SQL Server related services on one or more computers. In the table below you can see the parameters of Get-DbaService cmdlet as well as a friendly description.

Check Service Status

For example, if you want to check the status of the SQL Server services on computer SQL-A, then you should run the following command.

Get-DbaService -ComputerName SQL-A

Take a look at the results on the following screen capture.

Start Services

If we want to start a SQL Server related service using Dbatools we can use the Start-DbaService. On the table below you can see the parameters of Start -DbaService cmdlet as well as a description.

For example, if you want to start the default instance of SQL Server service on computer SQL-A, you have to execute the following command.

Start-DbaService -ComputerName SQL-A -Type Engine

On the next screen capture you can see the output of executing the query above.

Something to note is that if you omit the type of services with the –Type parameter Start-DbaService will try to start all SQL Server related services.

Stop Services

To stop SQL Server related services this module includes a cmdlet named Stop-DbaService which can be used to stop any of SQL Server related services on one or more computers. On the table below you can see the parameters of Stop-DbaService cmdlet as well as a friendly description.

Starting and Stopping SQL Server Services Using SQLCMD

SQLCMD has options to change the status of SQL Server related services, but it is not a method on its own. SQLCMD has the “!!” command to execute operating system commands. In order to do so we have to start a line with two exclamation marks (!!) followed by the operating system command.

For example, to start the SQL Agent service of the default SQL Server instance the command is

!!net start SQLSERVERAGENT

On the next image you can see a screen capture of how to start the SQL Server Agent service on the default instance.

Starting and Stopping SQL Server Service on Linux

On modern Linux distributions services are managed by the systemd service manager. Something to note is that systemd is not a utility used to start or stop services, it is a suite of building blocks on which Linux implements an elaborate transactional dependency-based service control logic. If we want to search for a Windows equivalent we can say that systemd is to Linux systems what Service Control Manager (SCM) is to Windows.

In order to interact with systemd to start, stop and restart services we use the systemctl command which syntax is as follows.

systemctl action service_name

For “action” we can use any of the following values expressed in the table below:

Sample Code

To start the SQL Server on Linux you can use the following command.

systemctl start mssql-server

If you want to stop the SQL Server on Linux then the following command will do the work.

systemctl stop mssql-server

But if you want to stop and start the SQL Server at the same time you can use the restart SQL Server option in the systemctl command as follows.

systemctl restart mssql-server

On the next image you will see a screen capture of a Linux terminal in Ubuntu on which I execute the systemctl command to show the status of the mssql-server service and start it.

Next Steps

• This tip covered the basic set of services of SQL Server. If you want to know more about all the services that make SQL Server, take a look at this tutorial: SQL Server 101 Components.
• Also, you can check this other tip to learn more about SQL Server services: Windows Services for SQL Server.
• In case you need to know which of the services is installed on your servers, in this tip you will learn how to use PowerShell to generate a list: Catalog SQL Server Services Installed on Servers.
• For more information about using the SQLCMD utility you can read my previous: Introduction to SQL Server’s sqlcmd utility.
• If you are new to PowerShell, then the following tip series is right for you: Introduction to Windows PowerShell for the SQL Server DBA Part 1.