cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

AppInsight of SQL - SQL Agent Job Component

I am working with AppInsight for SQL template, and trying to adjust component thresholds to reflect our event management requirements. One item that is particularly frustrating is the SQL Agent Job Component. No matter what I supply for warning or critical state it does not appear to follow similar rules in it's effect upon overall instance state.

For example, Buffer Cache Hit Ratio. If you leave the warning and critical thresholds empty (not configured) that component will always show green/up status regardless of current value collected. This in turn rolls up into the overall instance state contributing green/up status to the pool of other component monitors.

In the case of SQL Agent Job Info.  If you leave the warning and critical threshold values empty or set a very high number for warning and critical state on this component, if one job fails that component is identified in critical state and the instance is then identified in critical state.

Is there a way around this issue, minus disabling the SQL Agent Job component on the AppInsight template?

Labels (1)
Tags (1)
0 Kudos
20 Replies
Level 8

Hi Guys,

I too request the ability to filter SQL jobs as there are some that do fail that are not business critical. I still want the ability for specific SQL jobs to be left in the monitor that if they do fail, show representation of a business service failure. Can we please have this functionality added?

Thanks,

Leon

0 Kudos

SAM 6.2 (currently in beta) provides the ability to disable the SQL Agent Job info component monitor in AppInsight for SQL.

SQL Agent Job.png

0 Kudos

Unfortunately, the DBA's want to know when an agent job fails so I cannot disable this component.  But, it also marks the whole app as critical.  Can't it just mark the app as warning?

This goes back to one of my feature requests in that I would like to be able to select which, and/or how many components need to be "down" to reflect the app status.  Or the ability to select the overall app status if component X is not working correctly.

Ya know, the easiest thing would be if the "mixed status shows warning" worked as described.  But often times the mixed status will show critical.  Critical and/or red circles with exclamation points freak people out!

There are other ways of monitoring the SQL Agent job status outside of AppInsight for SQL. Below is one such example. Simply disable this component in AppInsight for SQL and use the following template instead.

Monitor State of SQL Server Agent Jobs

aLTeReGo wrote:

There are other ways of monitoring the SQL Agent job status outside of AppInsight for SQL. Below is one such example. Simply disable this component in AppInsight for SQL and use the following template instead.

Monitor State of SQL Server Agent Jobs

Actually, this method won't work for me due to the template requirements:

pastedImage_10.png

This does not account for Run Duration.  Plus - I have around 75 SQL servers with multiple jobs on each one.  It is not feasible for me to manually perform that many actions.  Thanks though!

Suggestions:

1. Separate the SQL Agent Job Info component into 2 components:

          a. SQL Agent Job Success/Failure

          b. SQL Agent Jon Run Duration

2.  Allow user to customize these components to reflect status (if match is found, up, down, etc...)

          a. SQL Agent Job if Succes, Then...

          b. SQL Agent Job if Failure, Then...

3.  Allow user to decide what makes an Application or group critical, warning, or down

          a.  By selecting essential components

          b.  By specifying how many components and.or which ones must be down before the app is down

          c.  By specifying a percentage of the group members must be in critical state before the group shows critical, same for warning, same for down.

Just my two cents - you're the man and know far better than I !!

Thanks for reading.

Thank ya sir!  What are your thoughts on why a group with mixed status shows critical rather than warning? Otherwise I would just select "show worst status".  Screenshots below:

pastedImage_2.png

pastedImage_1.png

0 Kudos

DBA's have reverted to alternate tool for SQL event management. I'll stay tuned to progress with SAM, but it's not a lead horse for us these days.

0 Kudos
Product Manager
Product Manager

There is not at this time. SQL agent job results are reflected in the SQL agent job status based upon the success or failure of the jobs configured to run on that SQL server.

0 Kudos

If we cannot put a threshold against this component on the AppInsight for SQL template, then there must be a way to disable that component. On the matter of not being able to set a threshold, why does the setting appear if it does nothing?

5-7-2014 9-26-50 AM.png

There are a number of jobs running in our environment expected to fail (frequently). SQL job failure in this regard is not critical. Tagging the entire instance critical due to one SQL job failure is defined as an annoyance after recent conversation with DBA leadership and could mean less reliance on the tool moving forward.

yervillageidiot, the Warning and Critical thresholds defined in your screenshot above are for the job duration, not success or failure. This allows you to be notified of runaway jobs, jobs that are hug, etc. 

Ah K, thanks for clarifying that aLTeReGo!

0 Kudos

That's all well and good, but that doesn't resolve the frustration of having an entire instance flagged as critical because 1 Job failed.  We also have 1 particular job on a server that fails frequently and flags the whole instance as critical.  It's very annoying to my DBA's who want to know when a Job fails, but it doesn't deem the entire instance to be in a critical state.  Please address this.

Yup +1 on this. It seems typical of the SQL environment to have failed jobs. Our DBAs have a similar approach to this - mostly they're not overly concerned. Would be nice to decide whether or not to have the status of this component roll up to the instance.

0 Kudos

I have logged this as a feature request which we are tracking internally under FB342956.

Is there any news on this request?  I am currently having the same issue and would like to disable this monitor.

0 Kudos

Nothing as of yet, but there also hasn't been a new release of SAM since this request was logged.

0 Kudos

OK  - 2.5 years later, any progress on being able to change the SQL job monitoring so a routinely failing insignificant job doesn't trigger a critical state for the SQL instance?

0 Kudos

Noapers.

In my role, I'm strictly an administrator and we have a team that creates and manages SSIS jobs, so unless we run out of disk space or memory, I don't care if a job fails.  I don't want to have to drill down into each server just to find that it's critical because a job failed 3 days ago.  I'd rely on Solar Winds more if this feature was more customizable.

+1

0 Kudos