Category: SQL Agent

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.

Leave a Comment

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.

Comments closed

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.

Comments closed

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.

Comments closed