re-run all the failed jobs under a SQL server 2000 installation.
TIA."NickName" <dadada@.rock.com> wrote in message
news:1105369505.976361.73090@.c13g2000cwb.googlegro ups.com...
> I'm wondering if there's an elegant/efficient way to automatically
> re-run all the failed jobs under a SQL server 2000 installation.
> TIA.
There's no one-click solution, if that's what you want - you would need to
write something yourself. You could create a proc which uses sp_help_job to
get job statuses, then sp_start_job to restart them. Another approach would
be to set each step in every job to go to step 1 on failure, so the job
keeps running until it succeeds.
Personally, I would avoid doing something like this anyway - it's rather
heavy-handed, and if you have job dependencies, critical scheduling issues
and so on, then you don't want jobs just starting at random times. Even if
every job you have now is independent of all others and could run at any
time, you don't don't know what future jobs you might add. And if a job
fails, I would prefer to investigate why, rather than simply keep running it
until it succeeds.
Simon|||You're right I'm not looking for a one-click solution, however, some
sort of automated way but still with some flexibility (as you correctly
pointed out "you don't know what future jobs you might add". Good to
know about sp_help_job and sp_start_job, they seem handy for the task
at hand, however, sp_help_job does not seem to help me much, I would
like it to list all the failed jobs for a given server, then loop
sp_start_job for each failed one. It would be desirable for
sp_help_job or the like to perform something like the above, that is,
EXEC sp_help_job @.execution_status = 'failed' | 0 (btw, currently the
@.execution_status values seems to be weird)
lists all the failed jobs
I appreciate your sharing of your knowledge, though I'm not a dba, it's
good to pick up some skill like this.
Don|||"NickName" <dadada@.rock.com> wrote in message
news:1105455394.853671.239030@.f14g2000cwb.googlegr oups.com...
> You're right I'm not looking for a one-click solution, however, some
> sort of automated way but still with some flexibility (as you correctly
> pointed out "you don't know what future jobs you might add". Good to
> know about sp_help_job and sp_start_job, they seem handy for the task
> at hand, however, sp_help_job does not seem to help me much, I would
> like it to list all the failed jobs for a given server, then loop
> sp_start_job for each failed one. It would be desirable for
> sp_help_job or the like to perform something like the above, that is,
> EXEC sp_help_job @.execution_status = 'failed' | 0 (btw, currently the
> @.execution_status values seems to be weird)
> lists all the failed jobs
> I appreciate your sharing of your knowledge, though I'm not a dba, it's
> good to pick up some skill like this.
> Don
This will list all failed job executions:
exec sp_help_jobhistory @.run_status = 0
Note that this will show all failures of all jobs (whereas sp_help_job shows
only the last run status for all _current_ jobs), so you might be looking at
a failure from 6 months ago for a job which no longer exists. Querying
sysjobs and sysjobhistory directly would give you more control (both tables
are documented in BOL).
But you should really consider concentrating on making sure that jobs do run
and reacting quickly when they don't, otherwise you may allow a situation
where job failures are considered normal, and therefore you never notice any
more serious issues (such as your backup jobs failing).
Of course, you may already have a good reason for doing this, and perhaps in
a development situation it might be useful.
Simon|||It would be really nice for the parameter of @.start_run_date to support
none equality operation, for instance,
exec sp_help_jobhistory @.run_status = 0, @.start_run_date > 20050108
would return all the failed jobs since Jan 8, 2005 instead of possibly
hundreds of rows/failed jobs.
Otherwise, have to use two loops of INNER and OUTER, just less
efficient.
And you're correct job failures are rare but it happens.
Thanks, Simon.
Don
