SQL Agent Insights:Documentation:Insight Metrics

Back to Documentation Main

---

A typical Insight from v0.27+

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 ms db.dbo.syssessions table.
• Recent – the total of attempted starts for the SQL agent service as indicated in the ms db.dbo.syssessions table over the recent time frame selected.

MS DB

• Size (MB) – the size of the MS DB database by summing the size of all files within ms db.sys.database_files.
• Last Full Backup (Hours) – a value indicating how long in the past that a full backup was done of the MS DB 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 ms db.dbo.sysschedules table.
• Enabled – of the entries in the ms db.dbo.sysschedules table, the number that are enabled and will be used by the agent.
• Disabled – of the entries in the ms db.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.

Error creating thumbnail: Unable to save thumbnail to destination 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 ms db.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 ms db.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.

Error creating thumbnail: Unable to save thumbnail to destination 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.
• Successful Outcome with Failed Steps (Entire History) – indicates the number of jobs that are showing successful within Job Monitor, however, have failed job steps within their execution history for all of the entire job history on the monitored instance.
• Successful Outcome with Failed Steps (Past 3 Days) – indicates the number of jobs that are showing successful within Job Monitor, however, have failed job steps within their execution history for the past 3 days.
• 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.

Error creating thumbnail: Unable to save thumbnail to destination Jobs that have never been executed take up space within MS DB. 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 ms db.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 ms db.dbo.sysmail_allitems table.
• Sent – the number of items contained within the ms db.dbo.sysmail_sentitems table.
• Failed – the number of items contained within the ms db.dbo.sysmail_faileditems table.
• Log Errors – the number of errors contained within the ms db.dbo.sysmail_log table.
• Log Events – the total number of records within the ms db.dbo.sysmail_log table of all event types.
• # of Mail Accounts – the total number of configured mail accounts as per the ms db.dbo.sysmail_account table.
• # of Attachments – the total number of mail items that had attachments as per the ms db.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 ms db.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 ms db.dbo.sysmail_profile table.
• Attachments Size (MB) – the size of all attachments in megabytes within the ms db.dbo.sysmail_attachments table.

Alerts

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

Error creating thumbnail: Unable to save thumbnail to destination 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 ms db.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 ms db.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 ms db.dbo.sysoperators table.
• Enabled – the total number of enabled operators within the ms db.dbo.sysoperators table.
• Disabled – the total number of disabled operators within the ms db.dbo.sysoperators table.
• Disabled with Alerts – the total number of disabled operators that have alerts configured.

Error creating thumbnail: Unable to save thumbnail to destination 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 ms db.dbo.syscategories table.
• Without Jobs – the total number of categories without jobs assigned to a category.

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.

---

Back to Documentation Main

---

Microsoft®, Windows® and SQL Server® are registered trademarks of Microsoft Corporation in the United States and/or other countries.
Some icons courtesy of Icons8