Failed jobs to successful


Quick and dirty solution to change failed jobs to successful : I have a new problem. Every time the server is restarted or taken out of maint mode (at least once a week) we will have a check of e.g failed jobs. We have several jobs that failed month ago and then is diabled. They will still give us alarms. To fix this we can do an quick and dirty fix. We can update the outome state from failed to successfull. This is how to do it:

In sql server 2005:

update msdb.dbo.sysjobservers set last_run_outcome =1 where last_run_outcome =0

in sql server 2000

sp_configure ‘allow update’, 1
go
reconfigure with override
go
use msdb
go
select * from sysjobhistory where run_status =0 and step_name = ‘(Job outcome)’
go
sp_configure ‘allow update’, 0
go
reconfigure with override

Note, this cod ecan be changed to just update one single job etc. It shoudl nto be used on jobs that is suppost to fail, e.g backup jobs etc. The purpose is to use it on jobs like SSIS test packages that fails adnt hen the csutomer waits for a nre version.

There is an nice stored procedure, sp_get_composite_job_info, that can be used ot get almost all information about a job.

Från Khan sql dba – mcitp www.addarr.com