4

I detected that SQLAgent was not running on one of our servers due to a unexpected memory leak error that caused SQL Agent to shutdown. There were not alerts configured so one main job that was supposed to be running there was not. Is there a way to monitor SQL Agent from SQL Server itself?

I've done lot of searches but can't find a way to do it. All I've found lot about monitoring jobs, schedules, etc. But not how to monitor if the SQL Agent stops working, does not restart or anything similar that prevents it to run.

PS: I'm planning on asking another question for the memory leak issue

4

What you can do is to have below script stored on your server or make it as a stored procedure :

use below with sqlcmd

IF EXISTS (  SELECT 1 
           FROM MASTER.dbo.sysprocesses 
           WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
   SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END 
ELSE 
BEGIN
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'databaseMail test profile',  --<-- Change HERE !!
    @recipients = 'databaseteam@somecompany.com', --<-- Change HERE !!
    @body = 'Please check the status of SQL Agent. It is not running !',
    @query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
    @subject = 'SQL Agent is not running',
    @attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END

enter image description here

What you can do to alleviate it :

enter image description here

  • Thanks, the last part of checking the checkboxes for autorestart was already done. But first recommendation to restart service in case of failure was not. Hope not to get same problem with this configuration. – Yaroslav Sep 5 '14 at 6:37
1

Your only option outside of setting up a separate process on another server is to configure the recovery actions for the specific service. This is done on the Windows side not SQL Server. Some folks will have it run a program/batch file that shoots an email or other such things. It is up to you.

Overall, if I did not have monitoring in place to handle this I would say just build a small PowerShell script that just sends an email if the service is found in anything but running and run it with a Scheduled Task on the server. Adding the above recovery options is only if the service is stopped unexpectedly, it will not cover if some program or script is run that gracefully shuts it down without your knowledge.

0

The hard way would be to build an extended event that watches for SQL Agent activity (it runs stored procedures periodically to check for jobs that need to be run) and notify if that doesn't happen. Problem is you'd need some sort of scheduled execution facility to run that, which is what you do with SQLAgent. And if you're going to use, say, the windows scheduler as a scheduled execution facility, it's probably better to just write some powershell to see if the service is running or not.

Better way is probably to tie into a service monitoring system (PRTG, System Center, etc) and use it to monitor your services. Using a service to monitor itself is a chicken and egg problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.