locked
SQL - Long Running Jobs Monitor RRS feed

  • Question

  • All,

    We've some SQL server jobs that run when SQL Server is started. But the SQL Management Pack is complaining about long running jobs. These jobs are designed to run long. Is there a way to tell Operations Manager that these specific jobs shouldn't be monitored for run duration?

    Best regards,
    Mark
    Tuesday, January 12, 2010 11:37 AM

Answers

  • Hi

    The default settings are for monitors targeted at SQL 200X Agent Job and SQL 200x Agent - go to authoring, monitors and scope for these classes.

    SQL Agent Job - Job Duration . The default behaviour is that you do not get an alert in the console. SQL Agent Job State goes Critical. This rolls up to SQL Agent which also goes critical):
    > Alert on state critical
    > Generate Alert = FALSE.

    SQL Agent - Long Running Jobs:
    > Alert on state critical or warning
    > Generate Alert = True

    The limitation with this is that the alert generating monitor is by default targeted at SQL Agent which means you can't override on per job basis.

    What you can do is change:
    - SQL 200x Agent Job > Performance > Job Duration - Threshold is in minutes. Set override to generate an alert = True FOR CRITICAL OR WARNING
    - SQL 200x Agent > Performance > Long Running Jobs . Set override generate alert = false.

    This means it is the job duration monitor that is generating the alert which is targeted at SQL jobs - so you should be able to override on a per job basis.

    Hope it helps

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    • Marked as answer by Mark Verbaas Tuesday, January 12, 2010 4:05 PM
    Tuesday, January 12, 2010 12:35 PM

All replies

  • Hi

    The default settings are for monitors targeted at SQL 200X Agent Job and SQL 200x Agent - go to authoring, monitors and scope for these classes.

    SQL Agent Job - Job Duration . The default behaviour is that you do not get an alert in the console. SQL Agent Job State goes Critical. This rolls up to SQL Agent which also goes critical):
    > Alert on state critical
    > Generate Alert = FALSE.

    SQL Agent - Long Running Jobs:
    > Alert on state critical or warning
    > Generate Alert = True

    The limitation with this is that the alert generating monitor is by default targeted at SQL Agent which means you can't override on per job basis.

    What you can do is change:
    - SQL 200x Agent Job > Performance > Job Duration - Threshold is in minutes. Set override to generate an alert = True FOR CRITICAL OR WARNING
    - SQL 200x Agent > Performance > Long Running Jobs . Set override generate alert = false.

    This means it is the job duration monitor that is generating the alert which is targeted at SQL jobs - so you should be able to override on a per job basis.

    Hope it helps

    Graham


    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    • Marked as answer by Mark Verbaas Tuesday, January 12, 2010 4:05 PM
    Tuesday, January 12, 2010 12:35 PM
  • Graham,

    A great solution! This works great for us.

    Regards,
    Mark
    Tuesday, January 12, 2010 4:05 PM
  • Hang on...  this is nonsense!  So, if I have a SQL Server instance with 10 jobs defined under the Agent, and only one of the 10 jobs runs long, I'm supposed to "globally deactivate" the alerts at the Agent level, then activate new alerts (back to default-like settings) for the nine jobs that I'm not worried about, then finally add a tenth one with "override" settings for the one job I started out to fix?

    There has to be a better way than this!

    Monday, March 29, 2010 1:26 PM
  • What Graham shows is that the alert source is changed.

    Normally at the Agent level an alert is generated when a job state is set to warning or critical.

    Each SQL (2005) job is an instance of the SQL 2005 Agent Job class. So what you Graham means:

    • Disable Long Running jobs alert on Agent level (which cannot be tuned for a specific job, which is what you want)
    • Enable Alerting for 'Job Duration - Threshold monitor' for all objects (just one override, you don't have to override this setting for each job!)
    • Override this monitor for your job and set the number of minutes.

    I think this works well.


    MCSE | MCITP | MCTS SCCM & SCOM 2007 | System Center tech blog: http://michielw.blogspot.com
    Tuesday, March 30, 2010 3:10 PM
  • How about enabling the "Discover SQL 2005 Agent Jobs" and then you can set it by a job per job basis for specific jobs.

    Monday, April 5, 2010 10:37 PM