Configure the SQL Agent Job Monitor in Operations Manager

I had to configure the SQL Agent Job Monitor for a customer and had some very interesting experiences while doing it.

The first fact to be aware about is that Jobs are not discovered by default. I believe Microsoft did that to avoid unnecessary cycles when not all jobs may be that critical.

If you want to enable the discovery of the jobs, you will have to first go to the Authoring area and scope as below:

image

Once you do that, your options should be as below when you select Discoveries on the Left side:

image

To enable the discovery, apply an override to either or both SQL 2008 and SQL 2012 Agents:

image

In this case, I have created a groups of SQL Servers that were important to have jobs monitored and applied the override to that group only.

Once the discovery runs, you should see the Agent Job State being populated:

image

image

Once you have the SQL Agent Jobs discovered the monitors will run by default, both last run states and Job Duration.

image

That’s when the fun starts. These are very peculiar monitors. Let’s take a look at each aspect of them.

1. The monitors are enabled by default, but don’t generate alerts. If you are looking to have alerts from them, you will need to apply an override:

image

2. For the Last Run Status monitor, the default behaviour is to send alerts (if you enable them) when the monitor is in a critical state. But surprise, this monitor never goes into a critical state!

image

So, even if you override it to send alerts, you will need an extra override for it to actually work:

image

3. Although the default value of the Alert Severity property is set to be Critical, when you get an alert, it will be a Warning alert,not critical. It’s not clear to me why, since all the configuration seems ok. If you really want the alert to be critical, you’ll need another override:

image

It really seems redundant, but it fixes the problem.

4. It seems that the Auto-Resolve Alert property also doesn’t work as expected. I have reset the health of the monitor and the Alert closed by itself, which I wouldn’t expect with the Auto-Resolve Alert set to false. The very likely reason for that you can see in the monitor properties:

image

So, if you want to change that, you will need to force the override below:

image

Once you get the alerts, they are a bit cryptic and not very informative:

image

image

For the SQL admin, those steps will likely make sense. Smile

 

Hope this helps!