Wednesday, August 15, 2012

SQL Agent Job -- Log More Information

1. in the script it runs, just add the print statement
2. change the step property to Apend output to step history.(Or define an output file for it.)
3. view the execution log using script. Here's one can be further modified.

select l.step_id,l.step_name,l.message,run_date,run_time,run_duration
from msdb.dbo.sysjobhistory l join msdb.dbo.sysjobs_view j
on l.job_id=j.job_id
where j.name='xxxxx'
and l.run_date='20120101'
and l.run_time>'10000'
order by l.run_time desc

The output will be appended to the job history. But make sure to limit the length under 1024 characters.

No comments: