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.
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
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.
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
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.
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.
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
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: