March 6, 2019 at 8:12 am
Hello All,
We have some issue with SQL agent job activity monitor. Sometimes, it doesn't show the job activity data. And we run job manually, we receive the error with failed to retrieve the data. Please find attached file for the reference. Any help would be greatly appreciate!
March 6, 2019 at 11:55 am
It almost looks like the Job History data is causing this. How many rows are there in the Job History? I've found if the Job History gets too big, the clean up process can start blocking writes and causing timeout errors. Right Click the SQL Server Agent > Properties > History
You need to balance the Max Rows Per Job, with the number of JOB Steps, and Max Log Size to get an optimal history and ensure you don't lose relevant history, but also ensure the table doesn't get too large.
Be careful because the Max rows per job counts rows at a STEP level, so a 10 step job stores 11 rows: One for each step, plus a Job Level row.
Generally I've found when the total number of rows gets up above 350,000 we start seeing performance problems with the background process that maintains the Job History tables, and this impacts Activity Monitor, etc..
Hope this helps.
Leo
Nothing in life is ever so complex that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 6, 2019 at 12:13 pm
Leo.Miller - Wednesday, March 6, 2019 11:55 AMIt almost looks like the Job History data is causing this. How many rows are there in the Job History? I've found if the Job History gets too big, the clean up process can start blocking writes and causing timeout errors. Right Click the SQL Server Agent > Properties > History
You need to balance the Max Rows Per Job, with the number of JOB Steps, and Max Log Size to get an optimal history and ensure you don't lose relevant history, but also ensure the table doesn't get too large.Be careful because the Max rows per job counts rows at a STEP level, so a 10 step job stores 11 rows: One for each step, plus a Job Level row.
Generally I've found when the total number of rows gets up above 350,000 we start seeing performance problems with the background process that maintains the Job History tables, and this impacts Activity Monitor, etc..
Hope this helps.
Leo
Nothing in life is ever so complex that with a little work it can't be made more complicated.
Job history setting:
Maximum job history log size (row) : 10000
Maximum job history rows per job: 1000
March 6, 2019 at 12:19 pm
EasyBoy - Wednesday, March 6, 2019 8:12 AMHello All,
We have some issue with SQL agent job activity monitor. Sometimes, it doesn't show the job activity data. And we run job manually, we receive the error with failed to retrieve the data. Please find attached file for the reference. Any help would be greatly appreciate!
You can also get that error when you have corruption. Make sure you have run dbcc checkdb on msdb.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply