For T-SQL Tuesday #186, Andy Levy asks, “How do you manage and/or monitor your SQL Server Agent jobs?”

This is a great opportunity for me to discuss how DBA Dash can help monitor SQL Agent jobs. DBA Dash is a free and open-source monitoring tool for SQL Server, created by me. It’s used to monitor thousands of SQL Server instances within Trimble alone, and it’s gaining popularity in the SQL Server community.

Built-in alerts for job failures

Before we discuss DBA Dash, what can we do within SQL Server without any 3rd party tools?

SQL Agent jobs have native alert notifications. You can get an email whenever a job fails, succeeds, or completes.

Issues with built-in alerts:

• If a frequently executed job starts failing, this can generate a lot of noise.
  A workaround is to set a retry policy and retry interval for your job steps.
• A lack of a failure notification doesn’t mean that everything is in a good state.
  You should validate the status of your SQL agent jobs in combination with alerts. Job activity monitor in SSMS could be used for this, but it doesn’t scale if you have many SQL instances to monitor.
• It requires database mail. There are no modern notification channels like Slack, Google chat, PagerDuty etc.

DBA Dash

DBA Dash includes several useful features to help you monitor your SQL Agent jobs.

Summary page

First up is the summary page. This is a single-page report that can tell you if you have failing jobs across your whole SQL Server estate. It can also tell you if your SQL Server agent is stopped on any of those instances. The summary page includes many other things you should include as part of your daily checks, in addition to monitoring agent jobs.

From the summary page, you can drill down and see specific jobs that are failing across your instances. You can click to view the failed steps and messages.

Thresholds and exclusions can be configured at the root, instance or job level.

Alerts

DBA Dash has an AgentJob alert rule that you can use for instant notifications. You can target a specific server, a group of servers (using tags), and filter by category and job name. Your notification options currently include Email, Slack, PagerDuty and Webhook (e.g. Google Chat).

This provides more modern notification options over built-in alerts and allows alerts to be configured centrally. You can control when you are notified and by what mechanism, depending on the priority of the alert.

Job Performance

You might also be interested in tracking job performance over time. DBA Dash allows you to do this centrally. The msdb database maintains a history of job execution, but the DBA Dash repository database enables you to store the execution metrics for a long time with minimal storage cost. You can also visualize the job performance over time very easily.

Timeline

The timeline view is a great way to see which jobs ran when and how long they ran for. This is useful to help plan job schedules, among other things.

Running Jobs

Want to know what jobs are running right now across all your instances?

Schema Tracking

DBA Dash scripts your SQL agent jobs, tracking when changes occur. This can be used to help answer what changed when and provides access to previous versions of jobs, should you need to roll back.

If you can source control your SQL agent jobs, that is also a great idea. DBA Dash can complement that.

This feature is also used to provide a SQL agent job DIFF tool so you can compare jobs between SQL instances.

More to come

As I’m writing this, I’m working on a Job Info page to provide job details on a single page (Steps, schedules, and other metadata). I’m using ScriptDom to parse this information, and with the DDL tracking, I can display this info for past versions of a job. I can also use this to display an alternative DIFF format.

DBA Dash includes a Running Query capture for performance monitoring (similar to sp_WhoIsActive or sp_BlitzWho). This will show you the job name if a query is associated with a running job. In the next version of DBA Dash, this will be a link, providing you with immediate access to the job details and execution stats.

These features should make their way into the next version of DBA Dash, 3.23. Or they might already be available by the time you read this.

Edit: 3.23 is here now! 🚀

Thanks for reading!