Dear All,

Two weeks back SQL Server Agent stopped on one of our servers and because of that all our backup jobs failed for the day. Fortunately nothing bad happened but after that incident I started looking for a way to check the status of SQL Server Agent and decided to change the format of our Daily Monitoring Reports also to incorporate the same.

In the absence of any monitoring tool it was becoming next to impossible to keep a check on the status of SQL Server Agent service on all our servers because of the workload we generally have on our site. This is in fact very dynamic site where we have to close a change request ticket within minutes.

Now the search begins and after a lot of search I found that I have two options, out of which first was to use some scripting language to get this done. But everybody is not aware of scripting languages and then to incorporate that in my daily monitoring reports was also a pain.

The second option was to use an extended stored procedure to check the status of service. So I used the second option.

The Extended stored Procedure is xp_servicecontrol

And the script to get the status of SQL Server Agent is

EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

In fact, you can check multiple other services also like messenger etc.

EXEC master..xp_servicecontrol 'QueryState', 'messenger'

Hope you find this useful. 

Regards,

Sarabpreet Singh