A typical active Insight showing all status types, including Healthy, Critical, Warning and Maintenance. Click to enlarge.

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 this value as close to zero (0) as possible.
  • 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 the running jobs each polling cycle, which can be toggled in the Insight Configuration.
  • 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: this is useful in potentially identifying jobs that can be removed.
  • 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: consider notifying, at least by email, all alerts that are enabled within the SQL instance.
  • 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 notifications may not reach that 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 if it is not going to be used.

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.