Press "Enter" to skip to content

Category: SQL Agent

Track those SQL Agent Jobs

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.

Leave a Comment

Managing SQL Agent Jobs with DBADash

David Wiseman shows off an open-source product:

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.

Read on to see how the product can help if you have a series of SQL Agent jobs.

Comments closed

Managing SQL Agent Jobs in a Large Environment

Steve Jones shares some tips:

I used to work in a fairly large enterprise (5,000+ people, 500+ production SQL instances) with a small staff. It was 2-3 of us to manage all these systems, as well as respond to questions/queries/issues with dev/test systems. As a result, we depended heavily on SQL Agent.

We decided on a few principles which helped us manage jobs, with a (slow) refactoring of the existing jobs people randomly created with no standards. A few of the things we did are listed below. This isn’t exhaustive, but these are the main things I remember.

Read on for Steve’s list.

Comments closed

SQL Agent “Success” on Failure

Todd Kleinhans does not believe that green is good:

Far too many times, I have seen DBA(s) and others have this false sense that if the Agent run status shows green, then everything must be ok.

Click through for a funny story about a gas station robbery and examples of how a SQL Agent job can report success but actually fail. You also see this a lot with replication or tasks that are asynchronous in nature: the task is reporting that we successfully started whatever operation, but that doesn’t mean the operation itself succeeded.

Comments closed

Self-Healing in SQL Agent Jobs

Rob Douglas doesn’t want to click the button a second time:

Recently, someone made an off the cuff comment about using TSQL Tasks in Maintenance Plans to handle more complex logic that SQL Agent handled out of the box. I was briefly excited by the prospect of building improved logic flows directly into SQL Server Maintenance Plans. This months Andy Levy hosts TSQL Tuesday and asks us about how we handle SQL Agent Jobs and this seemed like a great opportunity for me to share a story about how I wasted an afternoon testing a few components I have a passing knowledge with, attempting to implement auto-healing and conditional logic flow, only to snap out of it a few hours later when I realized that I was trying to solve a problem that someone else cracked nearly 2 decades ago.

Click through for the example. Some people might think of this kind of spelunking as a waste of time. My counter-argument is that it is better to know three ways of doing a thing versus one, as inevitably, you’ll run into the situation in which the one way is either not workable or is such a bad option that it’s painful to implement.

Comments closed

Visualizing SQL Agent Job History

Andy Levy wants a picture or at least a thousand words:

If you don’t have a monitoring suite watching SQL Agent, or you want to get a bigger picture view of when and how your Agent jobs are running, dbatools can help you shortcut getting this information. Get-DbaAgentJobHistory does exactly what its name suggests – it fetches the execution history for one or more Agent jobs on one or more SQL Server instances. Thing is…it can produce a lot of output if you’re not careful.

Read on to see how you can use this to generate a visual indicator of when your jobs are running and how they fare.

Comments closed

The Script-Based Nature of SQL Agent Jobs

Rob Farley praises the textual nature of SQL Agent jobs:

Of course, there are some things that I don’t like about SQL Agent. For example, I’ve never been a big fan of the Operator stuff. I’m sure there are organisations that leverage every feature of it, including pagers, but that has never been me or my clients. Reports that show the status of the jobs are generally more useful than simply relying on Job Notifications (although this also varies from client to client), but on the whole, it’s an area I’m okay, but not writing home about.

The thing I’m going to write about today though – one of my favourite aspects of SQL Agent Jobs – is that it’s all so scriptable.

Read on to learn more. I agree with Rob: it took a little while for me to understand (i.e., actually read what the job is doing), but I like that there’s nothing magical about SQL Agent and that you can simply put this into source control or even draft your own jobs without the UI once you understand the process well enough.

Comments closed