Learn more about SQL Server tools


Latest from MSSQLTips

Monitor, Start and Stop SQL Server services using xp_servicecontrol

MSSQLTips author Jugal Shah By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | 4 | More > Monitoring

As you know SQL Server runs using a bunch of different services in order for it to operate.  These can be monitored, started and stopped using SQL Server Configuration Manager or Windows Services, but this is not always the most convenient way of checking the services.  Is there any way this can be done within SQL Server Management Studio?  In this tip we take a look at a undocumented stored procedure that will allow you to do this.


To check the SQL Server services or any other service status using SSMS you can execute the extended stored procedure xp_servicecontrol.  This is un documented extended stored procedure.

EXEC xp_servicecontrol N'Querystat|Start|Stop',N'Service Name'

Use this command to check the Services status 
whether service is running or stopped
Use this command to start the service
use this command to stop the service


--See below example to check the status of SQL Services
EXEC xp_servicecontrol N'querystate',N'MSSQLServer'
EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
EXEC xp_servicecontrol N'querystate',N'msdtc'
EXEC xp_servicecontrol N'querystate',N'sqlbrowser'
EXEC xp_servicecontrol N'querystate',N'MSSQLServerOLAPService'
EXEC xp_servicecontrol N'querystate',N'ReportServer'
--See below example to start/stop service using SSMS
EXEC xp_servicecontrol N'stop',N'SQLServerAGENT'
EXEC xp_servicecontrol N'start',N'SQLServerAGENT'
--See below example to check non-SQL Service
EXEC xp_servicecontrol querystate, DHCPServer

To run this on multiple servers you can use SQLCMD as shown below or you can use Central Management Servers.

Step 1 Create "MyScript.sql" file using the below T-SQL:

set nocount on
CREATE TABLE #ServicesStatus
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate())
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'MSSQLServer'
update #ServicesStatus set serviceName = 'MSSQLServer' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT'
update #ServicesStatus set serviceName = 'SQLServerAGENT' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc';
update #ServicesStatus set serviceName = 'msdtc' where myid = @@identity;
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
update #ServicesStatus set serviceName = 'sqlbrowser' where myid = @@identity
select * from #ServicesStatus 
select char(13)
select char(13)
drop table #ServicesStatus

Step 2 Create a Windows batch file and save it with .bat extension. Add the list of server names you want to check as shown below
sqlcmd -SServerName1 -E -dtempdb -iMyScript.sql -W >>sqlServicesStatus.txt
sqlcmd -SServerName2 -E -dtempdb -iMyScript.sql -W >>sqlServicesStatus.txt
sqlcmd -SServerName3 -E -dtempdb -iMyScript.sql -W >>sqlServicesStatus.txt
sqlcmd -SServerName4 -E -dtempdb -iMyScript.sql -W >>sqlServicesStatus.txt
sqlcmd -SServerName5 -E -dtempdb -iMyScript.sql -W >>sqlServicesStatus.txt

Step 3 Execute the batch file and then open "sqlServicesStatus.txt" to see the status for all services that were checked.


If the service that you are checking does not exist you will get an error message like the following:
Msg 22003, Level 16, State 1, Line 0
OpenSOpenService() returned error 1060, 'The specified service does not 
exist as an installed service.'
Also, xp_servicecontrol is an undocument system stored procedure, so you will not get a support for it and moreover there is a chance of code changes or parameter changes, so be aware of this if you decide to use this.
Next Steps
  • Use the above or different commands with xp_servicecontrol extended stored procedure
  • Create a batch file by adding a number of servers and servicess with xp_servicecontrol to list out service status
  • Using this you can extend this to setup a monitoring process to check whether services are running as needed

Last Update: 6/11/2010

About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

print tip Print  
Become a paid author

Learn more about SQL Server tools

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

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Thursday, October 09, 2014 - 7:23:05 AM - Claudiu Read The Tip



It's not clear to me how can I use this to show me the services from another server. The serverName variable doesn't seem to be used.




Sunday, August 17, 2014 - 6:57:47 AM - Vishal Kumar Read The Tip

In my production server I have implemented this to monitor the service but after changed the default port from 1433 to 1435 this script stopped working. cauld you please let me know fi there is any specific solution to fix this issue.

Monday, June 23, 2014 - 11:11:20 AM - samuel Read The Tip

does this "xp_servicecontrol" SP support any SQL version (example 2012 or 2014 ) any and what recommendation to use it in production ?

Thursday, August 22, 2013 - 6:56:02 PM - mac Read The Tip


I am looking to find service status in 2000 server.i tried the scrpit which you pasted in your blog, but it is not working. 

i want to find service status from 2000 to 2008r2 servers.

can you please help me. 




Friday, May 25, 2012 - 2:51:32 AM - Phanisankar Read The Tip




even if i stopped SSAS from services still it's showing as service running. am i miss anything here?


Thursday, April 05, 2012 - 2:15:35 AM - sagar Read The Tip

Thanks for the tip, is there any option inerting all records in table....

Many thanks in advance

Wednesday, August 18, 2010 - 9:58:44 AM - Dr DBA Read The Tip

Here are some more info for analysis and full text search

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MSSQLServerOLAPService'  - Analysis Service

EXEC xp_servicecontrol N'QUERYSTATE',N'MSFTESQL '  -- Full Text Search Service


Does anyone now how to check to see if these, or others, services are not present? If a service say Reporting Services is not installed you get an error. I would be good to first check to see if the service is there then check its status

Thursday, August 12, 2010 - 3:23:17 PM - Dr DBA Read The Tip

 Love this tip! I would like to know how to check the Analysis and FulltextSearch service, any idea?


Friday, June 11, 2010 - 10:05:28 AM - admin Read The Tip

You could try something like this:

DECLARE @result nvarchar(255)
DECLARE @status table (result nvarchar(255))
INSERT @status EXEC xp_servicecontrol N'querystate',N'MSSQLServer'
SELECT @result = result FROM @status
IF @result = 'Running.'
PRINT 'Success'
PRINT 'Failure'


Friday, June 11, 2010 - 9:23:45 AM - tskelley Read The Tip

Thanks for the tip.  I have been looking into some type of health monitoring capability for our application that includes a mix of database and windows services.  As the author mentioned, this is an undocumented and therefore not supported by Microsoft.  See link below:

Usage of xp_servicecontrol is unsupported

Now for my question.  How would I select the results from this procedure into a variable, so I could report back to the user that the process failed?  Something like (but does not work):

DECLARE @result nvarchar(255)

EXEC @result = xp_servicecontrol N'querystate',N'sqlbrowser'

IF CHARINDEX('Running', @result) > 0

     PRINT 'Success'


     PRINT 'Failure'

More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.