Friday, July 6, 2012

xp_servicecontrol To Monitor Services Status From SSMS



To check the SQL Server services or any other service status using SSMS you can execute the extended stored procedure xp_servicecontrol. This is undocumented 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

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'   -- Checks the status of SQL Server Service
EXEC xp_servicecontrol N'querystate',N'SQLServerAgent'
EXEC xp_servicecontrol N'querystate',N'Spooler'

EXEC xp_servicecontrol N'start',N'SQLServerAgent'  -- Start the SQL Server Agent
EXEC xp_servicecontrol N'stop',N'SQLServerAgent'  -- Stop the SQL Server Agent

No comments: