SQL Agent Job Discovery not working?

First published on TECHNET on Jun 04, 2009

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

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

Log Name:     

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 FilesSystem Center 2007Health Service StateMonitoring Host Temporary Files 14595SQLAgentJobDiscovery.vbs(106, 5) Microsoft VBScript runtime error: Type mismatch

Command executed:    “C:Windowssystem32cscript.exe” /nologo “SQLAgentJobDiscovery.vbs” {974F57A5-5705-B6B2-B8DC-1CA0B433DCD4} {46913442-CAC1-7E38-89B4-1A6B462ED0D0} OMDW.opsmgr.net OMDW.opsmgr.net  OMDWI01 I01 SQLAgent$I01″

Working Directory:    C:Program FilesSystem Center 2007Health Service StateMonitoring Host Temporary Files 14595

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 , 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:

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:

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”):

Verify that the Agent Jobs are discovered:


Custom_SQL_Agent_Job_Discovery.zip

 

This article was originally published by Microsoft's Core Infrastructure and Security Blog. You can find the original article here.