Category: SQL Agent

Andy Brownsword takes a peek:

Most SQL Server environments have more jobs, schedules, and hidden complexities than you realise. It’s only when you arrive at a migration and peek under the hood that the scale is clear.

Here we’ll pull out details from msdb to give a clear snapshot of what you’ll actually be dealing with. If you don’t understand the effort upfront, the migration will expose it.

The thrust of Andy’s post covers migration, but I think it’s a good query to run simply to get a better understanding of all of the jobs in your environment.

David Plaut is looking for the root cause:

A SQL Server Agent job can start other jobs. Writing jobs this way makes it easy to compartmentalize jobs: start a “child” job only when the “parent” reaches a certain step. Finding these steps can be challenging. There is no field or property in sysjobs or its associated tables to help find child jobs.

Imagine this situation: Job B has started, and you don’t know why. You examine Job B, and it has no schedule. What started Job B?

Read on to learn how you can track down jobs that start other jobs, as well as a recursive lineage of who’s starting whom.

Jim Evans creates a job:

In this article, I’ll show how to create and schedule a SQL Server Agent Job. I’ll also show how to setup an Operator to receive notifications for failed or successful Job completions.

Jim lays out the UI-based approach and has scripting the job as an optional step. I highly recommend scripting and understanding the T-SQL it generates. It may look like a mess at first and the T-SQL it outputs is not idempotent (meaning, if you re-run the script multiple times, you do not end up with the same outcome and a successful run of the script). But changing this to become an idempotent template that successfully completes each time will allow you to store the code in source control and also build out future jobs that much faster. Additionally, it prevents issues where you have the “same” job but it’s actually set up differently across your different SQL Server instances.

Steve Stedman lays out the reasoning:

When managing SQL Server, SQL Server Agent Jobs are indispensable for automating tasks like backups, index maintenance, and data imports. However, monitoring these jobs can sometimes be tricky, especially when trying to quickly assess their status. One simple yet effective trick to improve visibility is adding a no-op (no operation) or logging step as the first step in your SQL Server Agent Jobs. This blog post explains why this small addition can make a big difference in monitoring and managing your jobs.

Click through for Steve’s argument.

Eitan Blumin shares some recommendations:

In many SQL Server environments, DBAs configure SQL Agent jobs with retry attempts (which is a smart move to handle transient issues gracefully). But with it comes a hidden pitfall that often goes unnoticed:

Retry-enabled job steps can silently overwrite logs or cause runaway log growth, depending on how the output file is configured.

The culprit? Improper use of append settings and missing time-based tokens in output file paths.

Read on to learn more.

Kevin Hill satisfies Betteridge’s Law of Headlines:

Too many IT teams treat SQL Server Agent jobs like a coffee timer “Set it and forget it!”

Unfortunately, that mindset only works if everything else is perfect forever. Whether its backup jobs failing silently, index maintenance running on the wrong replica, or nobody getting alerts when things break, unattended SQL Agent jobs are one of the sneakiest ways to rack up technical debt. Let’s dig into what DBAs and non-DBAs alike need to keep an eye on to avoid job-related headaches.

Kevin includes some good tips on monitoring SQL Agent jobs. If you’re feeling paranoid or have a particularly important job to watch, it may also make sense to set up some monitoring alerts around the end results, tracking things like the latest load date (for an ETL job) or some other indicator of doneness, and have a monitoring solution independently verify this.