Search code examples
sql-serverjobs

To obtain logs from the last 5 months that show jobs that have failed

I am trying to get information regarding the failed jobs for the last 4-5 months as a log file or any type of data. The jobs are run on the (MS)SQL server and are sql jobs.

My friend told me that the log files generated by the server get deleted every time the server is rebooted.

Is there any way to obtain this information.

Solution

  • No job history disappears when your server restarts, maybe you was told about SQL Server error log that initialize at every restart, but even previous error log does not disappear. In every case you can check your failed job this way:

    select j.name,
       j.description,
       h.*
from msdb.dbo.sysjobs j join msdb.dbo.sysjobhistory h 
        on j.job_id = h.job_id 
where h.run_status = 0 -- failed
order by h.run_date;

    You can check out how many history rows are preserved choosing SQL Server Agent Properties History tab.