3.17 MonitorJobs

Use this Knowledge Script to monitor the SQL Server scheduled jobs that have not successfully completed. You can specify the jobs to monitor. By default, this script raises events for the jobs that failed since the time you specify prior to the first iteration and thereafter for new jobs failure since the last monitoring interval. In addition, if the number of failed jobs exceeds the threshold you specify, an event is raised.

NOTE:To run this Knowledge Script, you need the SQL Server Select permission on sysjobs and sysjobservers tables of the msdb database.

Resource Objects

SQL Server instance

Default Schedule

The default interval for this script is every 10 minutes.

Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

General Settings

Job Failure Notification

Raise event if job fails unexpectedly?

Select Yes to raise an event if the SQLServer_MonitorJobs job fails unexpectedly. The default is Yes.

Event severity when job fails unexpectedly

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5.

Raise event if SQL Server login fails?

Select Yes to raise an event if login to SQL Sever fails. The default is Yes.

Event severity when SQL Server login fails

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15.

Raise event if job is running on SQL Express Server?

Select Yes to raise an event if a job is running on SQL Express Server. The default is unselected.

SQL Server jobs are not compatible with SQL Express Server. Therefore, if a database has an instance running SQL Express Server, AppManager raises an event that the job is running on SQL Express Server.

Event severity when job is running on SQL Express Server

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when a database is offline. The default is 15.

Additional Settings

 

Event Details

 

Event detail format

Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table.

Authentication

Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication.

User name

Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none.

For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide.

For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script.

Monitor SQL Jobs

Specify list of jobs to monitor (comma-separated)

Specify the names of jobs to include in monitoring. Separate multiple names with a comma. Only specified job names are included in data collection and have events raised if job failures are detected. You can use the SQL wildcard characters (% and _) to specify the names of the jobs. For example, if you specify App%, the jobs that start with App will be monitored.

By default, all jobs are monitored.

Monitor only new failed jobs?

Select Yes to monitor the failed jobs since the last monitoring interval. The default is Yes.

Specify the time prior to first iteration from which failed jobs are monitored

Specify the time (in minutes) prior to first iteration from which you want to monitor the failed jobs. For example, if you set the time as 20 minutes, the Knowledge Script starts monitoring for the failed jobs starting from 20 minutes before the first iteration. The default time is 10 minutes.

Number of SQL jobs to include in report

Specify the number of SQL jobs that a report can contain. The default is 20. Enter 0 to display all SQL jobs.

Include detail report in data points?

Select Yes to include a detail report in the data points collected for charts and reports. The default is unselected.

Event Notification

Raise event if failed monitor jobs exceed threshold?

Select Yes to raise an event if the number of job failures exceeds the threshold. The default is Yes.

Event severity when failed monitor jobs exceed threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of failed jobs exceeds the threshold. The default is 5.

Threshold - maximum failed monitor jobs

Specify the maximum number of failed jobs that can be detected before an event is raised. The default is 0 job.

Data Collection

Collect data for failed monitor jobs?

Select Yes to collect data for the total number of failed jobs for charts and reports. If enabled, data collection returns the number of jobs that have failed, including the job name and the reason for the failure. The default is unselected.