How to stop and start SQL Server services


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

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.

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.

Service Description
SQL Server (<Instance Name>) This is the database engine service. In other words, it’s the core of SQL Server.
SQL Server Agent (<Instance Name>) This service is used as a job scheduler by SQL Server. If this service is down, scheduled tasks like backups or index maintenance will not be executed.
SQL Server Browser This service helps client connections to obtain information needed to connect to SQL Server instances on the current server.
SQL Server CEIP Service (<Instance Name>) This is the Customer Experience Improvement Program service.
SQL Server VSS Writer Provides the interface to backup/restore Microsoft SQL server through the Windows VSS infrastructure.

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.

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).

These are the basic SQL Server services you will find.

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 manager.

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.

SQL Server service dependencies.

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 which is not running). In the next screen capture you will see it more clearly.

Using Service Manager to Start or Stop SQL Server related services.

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.

SQL Server Configuration Manager.

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.

You can start and stop SQL Server and SQL Server Agent from Object Explorer window of SSMS.

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.

Handling SQL Server engine status from Registered Servers window on SSMS.

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 use 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.

Service Default Instance Service Name Named Instance Service Name
SQL Server MSSQLSERVER MSSQL$<Instance Name>
SQL Server Agent SQLSERVERAGENT SQLAGENT$<Instance Name>

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.

Start and stop of SQL Server Agent service using net console command.

If you decide to use the SC command you will see that there are little 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.

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.

Obtaining SQL Server default instance status and starting it up using 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.

Obtaining status of SQL Server default instance on remote computer and starting it up using PowerShell.

Starting and Stopping SQL Server Services Using dbatools

Dbatools is a free PowerShell module which 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.

Parameter Description
ComputerName The server name or list of servers to get service information.
InstanceName The instance or list of instances to get service information.
Credential A PSCredential object (for more info on this check PowerShell Guide).
Type The type of service to get information. Can be one or a list of the following: "Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS", "PolyBase"

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.

Obtaining SQL Server related services status on remote computer using dbatools.

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.

Parameter Description
ComputerName The server name or list of servers to get service information.
InstanceName The instance or list of instances to get service information.
Credential A PSCredential object (for more info on this check PowerShell Guide).
Type The type of service to get information. Can be one or a list of the following: "Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS", "PolyBase"

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.

Remote starting SQL server engine service using dbatools.

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.

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.

Parameter Description
ComputerName The server name or list of servers to get service information.
InstanceName The instance or list of instances to get service information.
Credential A PSCredential object (for more info on this check PowerShell Guide).
Type The type of service to get information. Can be one or a list of the following: "Agent","Browser","Engine","FullText","SSAS","SSIS","SSRS", "PolyBase"
Force Forces dependent services to stop. For example if you want to stop SQL Server database engine service forcing SQL Server Agent to stop.
Remote stopping SQL server services using dbatools.

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 SQL Server Agent service from sqlcmd console.
Next Steps


Last Updated: 2020-02-06


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, February 07, 2020 - 7:46:11 PM - Tim Back To Top

You can also issue a TSQL Checkpoint command and then the TSQL Shutdown command to stop a service.



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools