Very slow SQL Server Agent history

Question

I have a big problem with the SQL Server Agent hostory. it has become VERY VERY slow when i try to view it. If I try through Management Studio I often get a timeout and if I try viewing it with sp_help_jobhistory it is just running for a long time.

I tried reducing the hostory size and the sysjobhistory table has only 8000 rows....

Is there any way to optimize this somehow?

Thx a lot Nicolaj

Answer 1

Ensure that the sysjobhistiory table is not fragmented. The easiest thing to do is reindex it. Since the table is so small, the reindex should happen quickly.

Answer 2

You could try to more regularly cycle (start new) your agent log. That can be done with EXEC msdb.dbo.sp_cycle_agent_errorlog; . Perform this periodically and your logs will remain both smaller and more manageable. (I generally create an Agent job that cycles the log weekly.) BTW, you can also cycle the error log as well with EXEC master.sys.sp_cycle_errorlog;.