Monitor, Start and Stop SQL Server services using xp

By: Jugal Shah | Comments (10) | Related: 1 | 2 | 3 | 4 | > Monitoring

Problem

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.

Solution

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.

Syntax

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

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

Examples

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

Notes

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

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Monitor, Start and Stop SQL Server services using xp_servicecontrol