SQL Agent Job Discovery not working?

re: SQL Agent Job Discovery not working?

Ravi — Fri, 24 Jul 2009 16:27:11 GMT

We have SCOM SP1 and all jobs have been discovered. However, the alert doesn't show the name of job. What should be done to have the Job Name in Alert?

Alert: Long Running Jobs

Source: SQLAgent$SCOMXY0798

Path: SQL Server Name;SQL Instance Name

Last modified by: System

Last modified time: 7/24/2009 7:13:05 AM Alert description: There are long running agent jobs on SQL instance SCOMXY0798 on computer SQLServerName. This may indicate an issue with one or more jobs.

re: SQL Agent Job Discovery not working?

jimmyharper — Tue, 04 Aug 2009 20:17:16 GMT

The alert that you posted is from the "Long Running Jobs" monitor, which targets the SQL Agent. This just tells you if the Agent has ANY jobs wich are running long.

For alerts on specific Agent Job duration, use the "Job Duration" monitor, targeted at "SQL 2005/2008 Agent Job". There is nothing in the alert description for this monitor, but the Source field will be the name of the Agent Job.

re: SQL Agent Job Discovery not working?

Shiva — Sat, 22 Aug 2009 00:39:40 GMT

Is it possible to tap into some kind of event notification / service broker message whenever a SQL Agent Job Completes (either succeeds or fails, doesn't matter) ?

I need to tap into sysjobhistory rows and copy them over in another db for long history analysis / trends. However, I am unable to find a way to do this as soon as a job is completed running.

Short of such an event, are my options restricted to simply polling the table for 'Job Outcome' step ? That seems terribly inefficient / improper approach...

thanks

re: SQL Agent Job Discovery not working?

nfomi — Mon, 31 Aug 2009 17:35:28 GMT

"So, what if you have a very large number of SQL Servers and Agent Jobs and do not want to worry about making sure that all of them have text in the Description field? To take care of this, I created a “workaround” version of the SQL Agent Job Discoveries that will discover these jobs and enters NULL for the NULL propeerty."

I wouldn't consider this a work-around. Rather, it's a fix to an inadequately designed script. A description is not required when create a SQL Server Agent job, and, in many cases (at least in our environment), is either not needed or is redundant because the name of the job accurately describes the tasks the job will perform. Any script/program that's going to process this data needs to be designed to correctly handle any valid data from the database.