Export SQL Server Agent Job History

March 22nd 2006 Microsoft SQL Server

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
Copyright
Creative Commons License