Applies to:

SQL Agent Insight version 0.22

Metrics

SQL Agent Insight has over 60 metrics to keep track of your monitored SQL Agent services. Some metrics are passive and some are active.

Passive metrics do not affect the overall status of the Insight, whereas active metrics, the sum of their individual status’ make up the overall health of the Insight.

The color of each individual metric indicates it’s current status, as per the last poll (either automatic or manual), of the SQL instance.

  • Green = Healthy
  • Red = Critical
  • Yellow = Warning
  • Gray = in Maintenance

For all “recent / recently” metrics below, the value represented for the metric is within the recent time frame selected, by default, this is a period of 3 days. Therefore, the metric represents a value from the time of the most recent poll minus 3 days. The recent time frame can be changed in the dropdown menu, More Options, in the toolbar of the Insight.

  • For example: Total sessions could indicate a value of 312, meaning that the SQL Agent has been started and has a record of 312 sessions.
    However, the metric for Recent sessions should indicate a value less than or equal to that of the total, meaning it has been started recently (and by default in the past 3 days), this number of times.

Overall

  • SQL Agent Service – passive – does not affect the overall status of the Insight.
    • A green checkmark indicates that the service is up and running, whereas a red x indicates that the service is stopped.

Sessions

  • Current Uptime (Hours) – indicates the length of time, in hours, that the SQL Agent service has been up and running since it’s most recent start.
  • Total – the total of attempted starts for the SQL agent service as indicated in the msdb.dbo.syssessions table.
  • Recent – the total of attempted starts for the SQL agent service as indicated in the msdb.dbo.syssessions table over the recent time frame selected.

MSDB

  • Size (MB) – the size of the MSDB database by summing the size of all files within msdb.sys.database_files.
  • Last Full Backup (Hours) – a value indicating how long in the past that a full backup was done of the MSDB database.
    • a lower value for this metric is better.
    • a value of 99999 indicates that a backup has never been completed or that a backup record does not exist on this instance of SQL Server.

Schedules

  • Total – the number of entries within the msdb.dbo.sysschedules table.
  • Enabled – of the entries in the msdb.dbo.sysschedules table, the number that are enabled and will be used by the agent.
  • Disabled – of the entries in the msdb.dbo.sysschedules table, the number that are disabled and will not be used by the agent.
  • Without Jobs – the number of schedules that do not directly reference an agent job.
  • Disabled with Jobs – the number of schedules that are disabled and also have an agent job associated with it. Therefore, if a schedule is disabled, the underlying agent job will not fire.
■ PRO TIP

Keep the value for Disabled with Jobs as close to zero (0) as possible. Having jobs that are enabled but are using schedules that are disabled will not fire.
  • Recently Created – the number of schedules recently created within the recent time frame selected.
  • Recently Modified – the number of schedules that have been modified within the recent time frame selected.

Jobs

  • Total – the number of jobs returned from the msdb.dbo.sp_help_job stored procedure.
  • Success Rate – an overall job success rate for the entire SQL Agent calculated as a percentage of the total number of job successes divided by the total number of job executions from all of the entries in the current msdb.dbo.sysjobhistory table.
  • Enabled – of the total jobs above, the number of them that are enabled.
  • Disabled – of the total jobs above, the number of them that are disabled.
  • Running – of the total jobs above, the number of them that are currently running as of the last instance poll.
■ PRO TIP

It may be useful to list running jobs during each polling cycle (detailed within the Insight Log), toggled in the Insight Configuration. Doing so during non-work hours can potentially show job conflicts that were previously unknown.
  • Succeeded – the number of jobs that succeeded on their last execution.
  • Failed – the number of jobs that failed on their last execution.
  • Cancelled – the number of jobs that were cancelled on their last execution.
  • Never Run – the number of jobs that have never run.
■ PRO TIP

Jobs that have never been executed take up space with the MSDB. This metric identifies the number of them which can be shown in the Job Monitor. Consider removing such jobs from this instance of SQL Server.
  • Enabled; No Schedules – identifies jobs that are enabled, but do not have a schedule, therefore they will not execute.
  • Enabled; No Notify – identifies jobs that are enabled and will run, but will not notify should they fail execution.
  • Recently Created – the number of jobs that have been recently created  within the recent time frame selected.
  • Recently Modified – the number of jobs that have been recently modified within the recent time frame selected.
  • Recently Failed – the number of jobs that have recently failed within the recent time frame selected.
  • Recently Cancelled – the number of jobs that have recently been cancelled within the recent time frame selected.

Database Mail

  • Status – passive – does not affect the overall status of the Insight.
    • Indicates the current state of the database mail queues as per msdb.dbo.sysmail_help_status_sp stored procedure.
      • A green checkmark indicates that the database mail queues are in a started state.
      • A red x indicates that the database mail queues are in a stopped state.
      • A yellow warning triangle indicates that the database mail queues are in an unknown state.
  • Total – the number of items contained within the msdb.dbo.sysmail_allitems table.
  • Sent – the number of items contained within the msdb.dbo.sysmail_sentitems table.
  • Failed – the number of items contained within the msdb.dbo.sysmail_faileditems table.
  • Log Errors – the number of errors contained within the msdb.dbo.sysmail_log table.
  • Log Events – the total number of records within the msdb.dbo.sysmail_log table of all event types.
  • # of Mail Accounts – the total number of configured mail accounts as per the msdb.dbo.sysmail_account table.
  • # of Attachments – the total number of mail items that had attachments as per the msdb.dbo.sysmail_attachments table.
  • Unsent / Retrying – the total number of mail items that have not yet been sent or are in a retrying state, per the msdb.dbo.sysmail_unsentitems table.
  • Recently Sent – the total number of mail items that have been recently sent within the recent time frame.
  • Recently Failed – the total number of mail items that have failed to send within the recent time frame.
  • Recent Errors – the total number of errors in the sysmail log table within the recent time frame.
  • Recent Events – the total number of log events in the sysmail log table within the recent time frame.
  • # of Mail Profiles – the total number of configured mail profiles as per the msdb.dbo.sysmail_profile table.
  • Attachments Size (MB) – the size of all attachments in megabytes within the msdb.dbo.sysmail_attachments table.

Agent Alerts

  • Total – the total number of alerts configured in the msdb.dbo.sysalerts table.
  • Enabled – the total number of enabled alerts within the msdb.dbo.sysalerts table.
  • Disabled – the total number of disabled alerts within the msdb.dbo.sysalerts table.
  • Enabled; No Notify – the total number of alerts within the msdb.dbo.sysalerts table that are enabled but do not notify.
■ PRO TIP

What is the point of having an alert configured and no one is ever notified when that alert fires? This metric will identify the number of these alerts and can be shown in the Job Monitor. Consider notifying, at least by e-mail, all alerts that are enabled within the monitored instance of SQL Server, or consider removing the alert completely.
  • Alert Occurrences – the total number of alerts that have fired across all configured alerts.
  • Alerts Reset – the total number of alerts that have been reset; that is, alerts that have a non-zero value for reset date and time within the msdb.dbo.sysalerts table.

Multi Server

  • # of Jobs – the total number of multi server jobs within this instance.
  • # of Targets – for a master server, the total number of multi server targets configured.
  • Recently Added Targets – for a master server, the total number of recently added targets.
  • Unread Instructions – the total number of unread instructions by a target within the msdb.dbo.sysdownloadlist, that is, their status is zero (0) and has not been downloaded by a target server.

Operators

  • Total – the total number of operators listed within the msdb.dbo.sysoperators table.
  • Enabled – the total number of enabled operators within the msdb.dbo.sysoperators table.
  • Disabled – the total number of disabled operators within the msdb.dbo.sysoperators table.
  • Disabled with Alerts – the total number of disabled operators that have alerts configured.
■ PRO TIP

If an operator is disabled and has notifications configured for that operator, important notifications may not reach them. Consider enabling the operator or change the alert to notify a different operator.
  • Enabled; No Notify – the total number of enabled operators without notifications.

Categories

  • Total – the total number of categories within the msdb.dbo.syscategories table.
  • Without Jobs – the total number of categories without jobs assigned to a category.
■ PRO TIP

Consider removing the category that does not have any jobs assigned to it and if the category is not going to be used in the future.

Proxies

  • Total – the total number of proxy accounts configured within this SQL instance.
  • Enabled – of the total number of proxies, the number that are enabled.
  • Disabled – of the total number of proxies, the number that are disabled.
  • No Job Steps – of the total number of proxies, the number that are not referenced in any job steps.
  • No Principals – the total number of proxies configured without any principals assigned.
A typical active Insight showing all status types, including Healthy, Critical, Warning and Maintenance.
Click to enlarge.