Job History Audit in SQL Server
Keeping track of SQL Server Jobs is an important part of maintaining a database proactively. The below script pulls a quick list of all jobs, completion status, and date the job ran. Taking this a step further one could have the SQL Server email them a copy of SQL Server Job History Report by adding taking advantage of the sp_sendmail stored procedure.

use msdb
select as 'Job Name', as 'Owner',
     case h.run_status
          when 0 then 'Failed'
          when 1 then 'Succeeded'
          when 2 then 'Retry'
          when 3 then 'Cancelled'
          else 'Other'
     end as 'Status',
     (convert(varchar, h.run_date) + ' ' + convert(varchar, h.run_time)) as 'DateTime'
from sysjobs j
inner join sysjobhistory h
     on j.job_id = h.job_id
inner join sys.server_principals sp
     on j.owner_sid = sp.sid
     j.enabled = 1
     AND h.step_name = '(Job Outcome)'

Share This