Как выключить sql server windows

How to stop and start SQL Server services

Please do not scroll away — stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

Let us help you stay informed and learn something new each day. Click here to keep informed.

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)

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 ( ) This is the database engine service. In other words, it’s the core of SQL Server.
SQL Server Agent ( ) 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 ( ) 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 , 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 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).

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 which 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 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$
SQL Server Agent SQLSERVERAGENT SQLAGENT$

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

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

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

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 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:

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

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.

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

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:

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

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

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

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.

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.

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.

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

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

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.

Источник

READ  Драйвера на принтер канон l11121e