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'

1 comment:

microsoft certifications said...

Dear Web site owner. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!
http://www.sqlservermasters.com/

Post a Comment