Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-04-04 : 16:54:28
|
| We have several jobs that if any step fails, we need to continue with the next step. Due to this we have the both 'On Success Action' and 'On Failure Action' set to 'Go to the next step'. Our only issue is that when all the steps are done we still require the job itself to fail.So my question is this, is there anyway the status of a job can be queried to determine if any of it's steps failed? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-04 : 16:59:53
|
| You can check Jobs history to find outorQuerysysjobsteps in MSDB |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-04 : 17:34:34
|
| How can I query the table to identify which records belong to the specific occurrance of the job? Other than joining to sysjobs and filtering for name = 'job name'. In other words if the job runs twice, how do I know which records are for each run? Or does this table only contain information for the currently executing job? |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-04-04 : 17:49:35
|
| I think that you need to check the table msdb.dbo.sysjobhistory.However I'm thinking if this really solves your problem. I understand it as you need the job to actually fail if any of the steps have failed. I don't know of any way to do that.Could you give us some more background on what the jobs are doing?Ola Hallengrenhttp://ola.hallengren.com |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-05 : 22:57:39
|
| >> how do I know which records are for each run?There are run date and run time columns in msdb..sysjobhistory table. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-07 : 18:28:06
|
| These are jobs that we have categorized as business type jobs. For example, one job that is called "Nightly Business Tasks". For simplicity lets say there are two (2) steps to this job. Now, each step is really only related to one another because they run nightly. Other than that the order of the steps is irrelevent. Lets say the steps are as follows:Step 1: Print Communications /* Assume this generates PDF files based on daily activity, zips them up as a batch and FTPs it to our printing vendor that collates, sorts, and mails stuff out */Step 2: Deactivate Unused Logins/* Assume this deactivates system logins for login accounts that haven't used the system in 120 days. This is a custom security model that utilizes user tables and not SQL logins */If step 1 fails I want step 2 to run. If we ever add a third step if step 2 fails I want step 3 to run.Right now we are configuring each step of the job to continue to the next step for both 'on success' and 'on failure'. At the end of the job there is a query that checks the msdb.dbo.sysjobsteps table for any records with a last_run_outcome = 0. If so then we perform a RAISERROR and have this step configured to send an email notification upon failure. However I'm not sure how reliable this is as it was written by a prior DBA no longer with us. I guess if the assumption that this table only holds records for the most recent job ran (or running) then this technique is valid. Any thoughts / comments?As far as msdb.dbo.sysjobstephistory, this table contains information for more than the current run so querying it would be a little more difficult I think. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-07 : 23:28:19
|
| You can look at max run date and max run time of the job only. |
|
|
|
|
|
|
SQL Server (2005) - SQL Job Steps / Error Handling