Have You Checked Your Jobs Recently?
Too many times when we’re reviewing a system to get our arms around what’s going on – we end up surprised by jobs that were relied on for processing, but that have long-since stopped working for whatever reason. This can really impair systems of course, and it usually ends up being one of those palm-to-the-forehead moments.
Here are a few considerations for your systems that might provide some help:
– Check Job Activity – In the Agent > Jobs > Job activity monitor you can see the last run status. This gives you a good overview of what’s-what, but it’s very limited as a status tool. The reason it can really surprise you is that if you rely on the "Last Run Outcome" you’re looking at only the last status. If the job fails intermittently, you can get an incomplete picture of what’s really going on.
To help remedy this, be sure you review histories for jobs – just right click on the job and select view history. This will give you a much better look at the jobs success and failure rate. It’s tedious, especially if you have a number of jobs, but this will at least give you a real look at the job’s success rate. Quick note – once you’re in there looking at history, you can review all the jobs and their histories, not just the one you may have selected. Just put a check mark next to the one you want to review.
– Watch the schedules – many, many times jobs that recur have bitten us. Without the right tools to watch over your system and see the schedule conflicts, it’s tougher to follow along and make sure you don’t have jobs stepping on each other. When you look at next run time, try to get a good idea of what else is running at that time. You can click on the heading to sort by the time and at least get a visual check.
If you have many jobs running at once, or if you’re seeing any kind of deadlock issue with your jobs fighting each other, you’ll want to review your scheduling, along with the recurring times for those schedules. You can quickly have situations where jobs start off independently running but that end up causing conflicts later in the scheduling day.
– Audit your jobs – make sure they’re working as you need. This seems pretty obvious, but the key here is that a lot of times the information you’re trying to work with exists within a "data window," perhaps a date range, and that data window, or the schema or … whatever, may shift. Your job may still run, may pull back data, but may need to be adjusted. Just know that they should be double-checked for results. Schema changes, data windows, filters, etc. – all play into this and can catch a job off-guard.