1. Запуск экземпляра сервера sql Server
В меню Пуск разверните подменю Все программы, Microsoft SQL Server 2008 R2, Средства настройки и укажите Диспетчер конфигурации SQL Server.
В левой панели диспетчера конфигурации SQL Server щелкните Службы SQL Server.
В панели подробных сведений щелкните SQL Server (MSSQLServer) правой кнопкой мыши и выберите Запустить.
Стрелка зеленого цвета на значке рядом с именем сервера и на панели инструментов обозначает, что запуск сервера прошел успешно.
Приостановка и повторный запуск экземпляра sql Server по умолчанию
В меню Пуск выберите Все программы, Microsoft SQL Server 2008 R2, Средства настройки и выберите пункт Диспетчер конфигурации SQL Server.
В диспетчере конфигурации SQL Server разверните узел Службы и щелкните элемент SQL Server.
В области сведений щелкните правой кнопкой мыши SQL Server (MSSQLServer), а затем выберите пункт Приостановить.
Пара синих вертикальных линий на значке рядом с именем сервера и в области инструментов указывает на то, что приостановка сервера прошла успешно.
Чтобы возобновить работу сервера, щелкните правой кнопкой SQL Server (MSSQLServer), а затем выберите пункт Возобновить.
Стрелка зеленого цвета на значке рядом с именем сервера и на панели инструментов указывает на то, что возобновление работы сервера прошло успешно.
Нажмите кнопку ОК, чтобы выйти из диспетчера конфигурации SQL Server.
Остановка и перезапуск экземпляра sql Server по умолчанию
…… В области сведений щелкните правой кнопкой мыши SQL Server (MSSQLServer), а затем выберите пункт Остановить (Перезапустить).
Запуск экземпляра sql Server из среды sql Server Management Studio
В папке зарегистрированных серверов или в обозревателе объектов щелкните правой кнопкой мыши экземпляр сервера, который нужно запустить, и выберите Запуск.
Значок в виде зеленой стрелки рядом с именем сервера указывает на то, что запуск сервера прошел успешно.
Приостановка и возобновление работы экземпляра sql Server
В окне «Зарегистрированные серверы» или «Обозреватель объектов» щелкните правой кнопкой мыши экземпляр сервера, который необходимо приостановить и выберите пункт Приостановить.
В появившемся окне сообщения необходимо подтвердить приостановку работы MSSQLSERVER на выбранном экземпляре сервера.
Нажмите кнопку Да.
Пара вертикальных синих полосок на значке рядом с именем сервера указывает, что сервер успешно приостановлен.
Чтобы возобновить работу сервера, щелкните правой кнопкой мыши экземпляр сервера и выберите пункт Возобновить.
В появившемся окне сообщения необходимо подтвердить возобновление работы MSSQLSERVER на выбранном экземпляре сервера.
Нажмите кнопку Да.
Зеленая стрелка на значке рядом с именем сервера указывает, что сервер успешно возобновил работу.
Остановка экземпляра sql Server
В зарегистрированных серверах или обозревателе объектов щелкните правой кнопкой мыши экземпляр, который нужно остановить, и нажмите кнопку Остановить.
Появится сообщение, в котором нужно подтвердить намерение остановить MSSQLSERVER на экземпляре выбранного сервера.
Нажмите кнопку Да.
Красный квадрат на значке рядом с именем сервера означает, что сервер успешно остановлен.
Name already in use
sql-docs / docs / database-engine / configure-windows / start-stop-pause-resume-restart-sql-server-services.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Start, stop, pause, resume, and restart SQL Server services
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.
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.
Database Engine 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.
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.
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.
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 SQL Server Database Engine
Start SQL Server Configuration Manager, using the instructions in the previous section.
If the User Account Control dialog box appears, select Yes.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services.
In the results pane, right-click SQL Server (MSSQLServer) or a named instance, and then select Start, Stop, Pause, Resume, or Restart.
Select OK to close the 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
Start SQL Server Configuration Manager, using the instructions in the previous section.
If the User Account Control dialog box appears, select Yes.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services.
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.
Select OK to close the SQL Server Configuration Manager.
[!NOTE]
SQL Server Agent can’t be paused.
SQL Server Management Studio
Start, stop, pause, resume, or restart an instance of the Database Engine
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.
If the User Account Control dialog box appears, select Yes.
When prompted if you want to act, select Yes.
Start, stop, or restart an instance of the SQL Server Agent
In Object Explorer, connect to the instance of the [!INCLUDEssDE], right-click SQL Server Agent, and then select Start, Stop, or Restart.
If the User Account Control dialog box appears, select Yes.
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 Database Engine
From a command prompt, enter one of the following commands:
Start a named instance of the Database Engine
From a command prompt, enter one of the following commands. Replace <instancename> with the name of the instance you want to manage.
Start the Database Engine 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 (-).
Start the SQL Server Agent on the default instance of [!INCLUDEssNoVersion]
From a command prompt, enter one of the following commands:
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.
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:
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 net stop.
The [!INCLUDEssDE] can be stopped by using the SHUTDOWN statement.
Stop the Database Engine 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.
To stop the [!INCLUDEssDE] immediately, execute the following statement.
For more information about the SHUTDOWN statement, see SHUTDOWN (Transact-SQL).
Start and stop Database Engine services
In a Command Prompt window, start [!INCLUDEssNoVersion] PowerShell by executing the following command.
At a [!INCLUDEssNoVersion] PowerShell command prompt, by executing the following command. Replace computername with the name of your computer.
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].
To get a reference to a named instance of the [!INCLUDEssDE].
To get a reference to the SQL Server Agent service on the default instance of the [!INCLUDEssDE].
To get a reference to the SQL Server Agent service on a named instance of the [!INCLUDEssDE].
To get a reference to the SQL Server Browser service.
Complete the example to start and then stop the selected service.
Check and enable disabled instances
To determine whether a SQL Server service instance is disabled, follow these steps:
Identify the service that you’re trying to check by using the information in the Identify the Service section.
In SQL Server Configuration Manager, select SQL Server Services and then locate the service you’re interested in.
If the value of the Start Mode column is set to Other (Boot, System, Disabled or Unknown), it typically means the corresponding service is disabled. To enable the service, follow these steps:
a. In the Name column, right-click on the corresponding service and then switch to Service tab in the <Service name> Properties window.
b. Review the value in the Start Mode column and verify that it’s set to Disabled.
c. Change the value to either Manual or Automatic per your requirements. For more information, see Configure Server Startup Options (SQL Server Configuration Manager).
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.
How to stop and start SQL Server services
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).
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$<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
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.