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.

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

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: