Home / server / Questions / 397497
Accepted
NicolajB
Asked: 2012-06-12 00:18:52 +0800 CST

Very slow viewing the SQL Server Agent history

  • 772

I have a big problem with the SQL Server Agent history. 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 history size and the sysjobhistory table has only 8000 rows....

Is there any way to optimize this somehow?

sql-server-2008

2 Answers

  1. Best Answer

    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.

    • 3

  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;.

    • 0