Monitor SQL Server and related services using T-SQL | SansSQL

Saturday, May 18, 2013

Monitor SQL Server and related services using T-SQL

As part of the DBA job, it is very important to monitor the SQL Server and its related service and ensure that the services are always up and running.
There are different ways to achieve this, and one among them is by using the custom SQL scripts.
Writing custom SQL scripts play a vital role in few environments where budget is a concern to implement an full fledged monitoring system.

Below is a T-SQL query which uses an undocumented extended stored procedure "xp_servicecontrol" which tells the status of the service.
You can use this query in a CMS or by using a multi server query to record and display the status the service.
DECLARE @ServiceStatus TABLE
(ServerName nvarchar(50)
,ServiceName nvarchar(50)
,StatusOfService nvarchar(20)
,StatusAsOn datetime)

INSERT INTO @ServiceStatus (StatusOfService)  
EXEC master..xp_servicecontrol 'QueryState', 'MSSQL'
UPDATE @ServiceStatus 
  SET ServerName=@@SERVERNAME
  ,ServiceName='MSSQL Server'
  ,StatusAsOn=GETDATE() 
WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)  
EXEC master..xp_servicecontrol 'QueryState', 'SQLAgent'
UPDATE @ServiceStatus 
  SET ServerName=@@SERVERNAME
  ,ServiceName='SQL Server Agent'
  ,StatusAsOn=GETDATE() 
WHERE ServerName IS NULL

INSERT INTO @ServiceStatus (StatusOfService)  
EXEC master..xp_servicecontrol 'QueryState', 'SQLBrowser'
UPDATE @ServiceStatus 
  SET ServerName=@@SERVERNAME
  ,ServiceName='SQL Server Browser'
  ,StatusAsOn=GETDATE() 
WHERE ServerName IS NULL

SELECT * FROM @ServiceStatus

In this query, I have included the SQL Server Service, SQL Agent Service and SQL Browser Service.
This can be used in a scheduler and send an email with the status of services which are not running, etc...

We can also do much more than just querying the status of the service using this undocumented stored procedure "xp_servicecontrol".
We can start, stop, pause and continue a service using this.
EXEC master..xp_servicecontrol @Action = 'Action' @ServiceName = 'Service Name'

No comments:

Post a Comment

Ads