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