Share via

SQL Agent Job Discovery not working?

  • Article

The SQL Server Management Pack includes an option to discover and monitor SQL Server Agent Jobs for SQL 2000/2005/2008.  The Discovery for this is disabled by default:

image

I ran into an issue recently where all Agent Jobs for specific SQL Servers were not being discovered.  Examining the event logs on the SQL Server, we see the following in the OpsMgr Event Log:

Log Name: Operations Manager
Source: Health Service Modules
Date: 6/4/2009 8:36:19 PM
Event ID: 21406
Task Category: None
Level: Warning
Keywords: Classic
User: N/A
Computer: OMDW.opsmgr.net
Description:
The process started at 8:36:18 PM failed to create System.Discovery.Data. Errors found in output:

C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 1\4595\SQLAgentJobDiscovery.vbs(106, 5) Microsoft VBScript runtime error: Type mismatch

Command executed: "C:\Windows\system32\cscript.exe" /nologo "SQLAgentJobDiscovery.vbs" {974F57A5-5705-B6B2-B8DC-1CA0B433DCD4} {46913442-CAC1-7E38-89B4-1A6B462ED0D0} OMDW.opsmgr.net OMDW.opsmgr.net OMDW\I01 I01 SQLAgent$I01"
Working Directory: C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 1\4595\

One or more workflows were affected by this. 

Workflow name: Microsoft.SQLServer.2008.AgentJobDiscoveryRule
Instance name: SQLAgent$I01
Instance ID: {46913442-CAC1-7E38-89B4-1A6B462ED0D0}
Management group: PROD1

The “Type mismatch” error typically means that some variable in the script is returning with an incorrect data type.  After examining the Discovery script and doing to troubleshooting, we determined that the problem was happening because the “Description” field for the Agent Job was NULL.  This can be confirmed by running the “sp_help_job” Stored Procedure against the MSDB database on the SQL Instance (which is exactly what the Discovery script does:

image

 

This will also happen if any of the following properties of the job are NULL:

job_id
originating_server
name
description
category
owner

We probably won't ever see this with the job_id, originating_server, category or name properties, but we've seen it with the description and owner properties.

To correct this, we can simply enter some text in the description field of the Agent Job:

image

Note that the problem described above (Agent Job discovery failing when properties are NULL) happens on SQL 2005 and 2008 Agent Jobs.  SQL 2000 Agent Job Discovery does not use the VBScript, and does seem to work in this scenario, but the NULL values are populated with the values of other Agent Jobs, so it is not accurate.

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.

  1. Import the “Microsoft.SQLServer.200x.Discovery.CustomAgentJobDiscovery.xml” management pack
  2. Disable the original Agent Job Discovery and enable the new one (“SQL Server 200x Custom Agent Job Discovery”):

image

Verify that the Agent Jobs are discovered:

image

Custom_SQL_Agent_Job_Discovery.zip

Comments

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    July 24, 2009
    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.

  • Anonymous
    August 21, 2009
    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

  • Anonymous
    August 31, 2009
    "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.

  • Anonymous
    January 14, 2011
    Anyone has customized the long running job monitor? I tried but it did not work. current SQL MP has long running job monitor but it monitors all SQL job which i do not want to do. There is no option to override it. I can not use job duration monitor because it works based on historical data rather long running job calculate based on live data.

  • Anonymous
    June 28, 2011
    Hello, I have imported the Management Pack blogs.technet.com/.../sql-agent-job-discovery-not-working.aspx and enabled Discover SQL 2008 Agent Jobs (Custom) and Discover SQL 2005 Agent Jobs (Custom ) under Object Discoveries but it seems the thresholds of 6000 seconds is delaying the discovery, any log to review to be sure the process is on-going as I don't see any job under the SQL Agent State View? Could it be only the threshold? I have restarted the System Center Management Service on two machines but still nothing yet... Thanks, Dom

  • Anonymous
    June 29, 2011
    The comment has been removed