You are here: Home > SQL Agent > Job Steps
Archive | Job Steps RSS feed for this section

A Table Summarizing All Agent Jobs with Steps…

by on April 19, 2009

Also on the topic of SQL Agent jobs– each time I work with a new system, it can take a while to familiarize myself with what all the Sql Agent jobs do. Often there are quite a few jobs, and sometimes they have legacy names that either don’t describe what the job does very well anymore, or is just hard to understand.

Plus, I don’t like opening jobs in the SQL Agent itself very much, since it only opens in an ‘edit’ view. I very much prefer selecting job details out of the tables in msdb, it’s just safer.

Because of this, a while back I wrote a SQL script that takes a lot of descriptive information about a job in MSDB and pivots it out into a table. The table will automatically have as many columns as are required– I have a server where a job has 41 steps, so it’s got 41 columns for step, each in order.
Continue Reading →

Comments { 0 }

SQL Agent Jobs- Checking for failed steps at the end of a job.

by on April 18, 2009

I use the SQL agent a lot, and it is handy for a lot of things, but  it can be frustrating to not be able to pass state information between steps.

For example, I have a job where I want to execute data verification steps against multiple tables. It makes sense to have the check for each table in its own step with a clear label to simplify troubleshooting– so when the job fails, you can see which step had an error and know from the name exactly what’s wrong.  But I want all steps in the job to run, regardless of whether a step fails— I want to check for failure at the end.

The most basic way to do this is to have each job step log to a table. This isn’t really bad, but I’d rather not maintain a table for every job of this type. It leaves room for failure, it’s more to maintain, and it just feels redundant for  the most part: all of the job history  is tracked in MSDB anyway, shouldn’t I be able to use that?

Well, I think I can…
Continue Reading →

Comments { 2 }