Sql-server – Hi folks, In one of the Agent Job is running by every minute

jobssql serversql-server-agent

One of my SQL agent Job is running every minute, the job has failed . I need the job history of past five days.

Is there any way we can get the history details?

I have searched in the log but not able to find the details.

Please help me.

Best Answer

Depending on how you have configured the history for the Sql Agent, you may have lost that information. From this answer:

The SQL Server Job system limits the total number of job history entries both per job and over the whole system. This information is stored in the MSDB database.

Obviously you won't be able to go back and see information that has been since discarded, but you can change the SQL Server Agent properties and increase the number of entries that will be recorded from now on.

In the SQL Server Agent Properties:

  • Select the History page
  • Modify the 'Maximum job history log size (rows)' and 'Maximum job history rows per job' to suit, or change how historical job data is deleted based on its age.

It won't give you back your history, but it'll help with your future queries!

You might be able to tweak the following query to give you the information you're looking for (if it still exists). I got the query from here.

;WITH jobListCTE as
(
SELECT j.name as job_name,
msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
message
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.step_name = '(Job outcome)'
)
SELECT job_name as [JobStep],
run_datetime as [StartDateTime],
SUBSTRING(run_duration, 1, 2) + ':' +
SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) as [Duration],
message
FROM jobListCTE
ORDER BY run_datetime DESC, job_name;
Related Question