May 24, 2011 at 4:34 am
Hello,
On our instance, all job history is deleted after 4 days.
We've test to disable our purge jobs, disable the policy's,...
And at the SQL Server Agent: settings for "Job history log" are checked too, but still ... all job history is deleted after 4 days.
Is there another setting/configuration I overlooked?
On his brother, the job history is there (30days, configured by our purge history job) and those 2 SQLservers have the same setup...
Can someone give me another tip?
May 24, 2011 at 4:52 am
Stupid me.. found the problem.
I had default settings for 'Maximum job history log size (rows)' = 10.000
Now it seems that this causes the 'problem':
Running following script, gave me 9.999 records, so it's just unhappy setting where it looked like just 4 days....
USE msdb
Go SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name, h.run_date, h.run_time
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply