SQL Job History Retention 2

[Ovatvvon]

In Enterprise Manager you can right-click on a SQL Job and select 'view history' to see the history of the successes/failures of that job. Our job currently only goes back between 2 and 3 days, and I need to see beyond that. I realize that by extended the timeframe of history retention I will not be able to see further back in time right now, but, is there a way to change the length of time history is retained for the results of SQL Jobs? Where can that be done?

-Ovatvvon :-Q

 

[ptheriault]

It is actually limited to a number of rows in the table. For example I have a job that has a huge amount of steps in it. over 1000. I only get 1 days history. Unfortionatly I don't believe that parameter can be changed.


- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

Someone else told me that in the mainenance plan, if you set the history rows (i.e. 1000) on the reporting tab, that that will affect it. To me, this looks more like an error report for backing up databases, etc, not for all history retention in SQL Server.

Do you think it would work for that? It would be very nice if there was a setting that you could extend the number of rows / timeline for history retention.


-Ovatvvon :-Q

 

[ptheriault]

The maint plan history is not stored in the same tables as the job history. job history is stored in sysjobhistory in the msdb database. maint plan history is stored in the sysmaintplan_history table in the same db. You can control how much history you save for a maint plan but now for a job.

- Paul
- Database performance looks fine, it must be the Network!

 

[Ovatvvon]

Ok, thanks for explaining that! This helps!

-Ovatvvon :-Q

 

[mrdenny]

Job history settings can be changed by right clicking on the SQL Agent (in Enterprise Manager), and clicking properties.

In SQL 7 and SQL 2000 go to the Job System Tab. Change the Maximum job history log size and maximum job history rows per job.

In SQL 2005 to to the history page and change the settings listed above.

Do not uncheck the Limit size of job history log as this will quickly fill the msdb database as well as make viewing the job history MUCH slower over time.

If I need to change it I typically change the numbers from 1000 and 100 to 10000 and 1000. This will usually give enough job history to be of more use.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

Perfect!!! Thanks Denny!

-Ovatvvon :-Q

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com