Export SQL Server Agent Job History
The following query is a good starting point if you want to export the SQL Server Agent job history to a file and you're still using Enterprise Manager from SQL Server 2000. In SQL Server Management Studio from SQL Server 2005 there's already a command available to do this in the Log File Viewer window accessible from the View History command on the selected job.
USE msdb
SELECT
J.name,
S.step_id,
S.step_name,
H.message,
run_status = CASE H.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END,
H.run_date,
H.run_time,
H.run_duration
FROM sysjobhistory H
INNER JOIN sysjobsteps S ON H.step_id = S.step_id AND H.job_id = S.job_id
INNER JOIN sysjobs J ON J.job_id = H.job_id
ORDER BY H.job_id, H.run_date, H.run_time, S.step_id
