Skip to content

How to Restart SQL Server Services

February 14, 2013

Solution: There are cases when you will have to restart your SQL Server Services example after adding a trace flag, changing configuration settings etc. there are different methods that you can use to restart services. I will cover some of them in this article.

Option 1:  Stop Start SQL Server Services from Configuration manager: The most used and recommended method is to restart Services using SQL Server Configuration manager, you can find SQL Server configuration manager under All programs → Microsoft SQL Server → SQL Server Configuration Manager → SQL Server Services → Right Click service and select restart.

Refer Screenshot below:

RestartSQLServices.1

Option 2: Restart Services from Service Console: Open service control manger either by typing services.msc on run prompt or using services Console shortcut under control panel → Administrative tools → Services. All the SQL server services will start with SQL Server → Find the service right click and restart.

Refer Screenshot below:

RestartSQLServices.2

Option 3: We can restart SQL Server Services from management Studio using “xp_servicecontrol”

Syntax: xp_servicecontrol @Action = ‘<Action>’ @ServiceName = ‘<Service Name>’

Action: Start (Will start the service if stopped), stop (will stop the service if started), querystate (returns the current state of the service.). Service Name (name of the service, to get service name open services console shown in step 2 → Right click service → properties → Check Service name). Note: There is no action as “restart” so in order to restart a service you have to stop and start it.

Example:

xp_servicecontrol @Action ='querystate',@ServiceName ='SQLSERVERAGENT'
go
xp_servicecontrol @Action = 'querystate', @ServiceName = 'MSSQLSERVER'

Note: Do not restart MSSQL Service using above command, there are couple of reasons for not doing it as mentioned below:

  • SQL Server agent is dependent on SQL Services so before stopping MSSQL Server agent service needs to be stopped. If you directly try to stop MSSQLServer service it will give an error.
  • If you stop SQL Server service services you will not be able to execute start command as no query can be executed on an instance whose MSSQLServer service is down. So you will have to then manually start the service using setp 1 or setp 2.

This command is more relevant to quickly checking status of any service like the reporting service, Browser service, MSDTC etc.

Option 4: Stop Start SQL Server Services using command line:

Login to server → Open Command prompt (Where is command prompt? You can open it by typing cmd on run prompt or you can find command prompt under accessories), use below command to stop start SQL Server Services.

To Stop Service:

C:\>net stop <Servcie name>

Example: C:\>net stop MSSQLSERVER – will stop SQL Server Service on server

———————

To Start Service:

C:\>net start <Servcie name>

Ex: C:\>net start MSSQLSERVER – will start SQL Server Service on server

Important Points:

  • To stop and disable a service(Ex: during server Decommission) use Service console or configuration manager.
  • If you want to change start up parameters for any service then you have to use configuration manager.
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: