SQL agent job history retention can not be set

Question

Hi All,

we've installed the SQL server 2008 R2 64bit, but when I try to access "SQL Server Agent" properties to set the history retention, it's failed. It doesnt reflect the changes made and goes back to the default settings.

Can you share your experience on that? for me it's very improtant.

Many thanks in advance.

 

Answer 1

Unfortunately there has been a bug with this in SSMS for quite a while and it appears that it hasn't been fixed.  It only has 4 up-votes at the current time, which is pretty low, but definitely has an impact to those who encounter it.  A workaround is provided in the Connect bug below and also in the threads below where people typically use the History Cleanup task in Maintenance Plans. 

https://connect.microsoft.com/SQLServer/feedback/details/485232/kjtap-sap-sql-server-agent-remove-agent-history-cannot-be-activated

http://www.sqlservercentral.com/Forums/Topic1267858-1550-1.aspx

http://social.msdn.microsoft.com/Forums/sqlserver/en-us/3b3983e1-340c-4169-a616-9a4cd76402a4/sql-server-agent-remove-agent-history

Thanks,
Sam Lester (MSFT)

---

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Hello,

As a "workaround" you can use the following T-SQL script / sp_set_sqlagent_properties to change the job history retention; in this sample to 2000 total/200 per job:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
     @email_save_in_sent_folder=1, 
     @use_databasemail=1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
     @jobhistory_max_rows=2000, 
     @jobhistory_max_rows_per_job=200, 
     @email_save_in_sent_folder=1, 
     @use_databasemail=1
GO

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Thanaks for your prompt reply. For me, do you have t-sql to make sql server save 60 days ago histroy?

I saw your script it's doing increase job row.

Thanks.

Answer 4

You can't limit it for a time period like 60 days, only for the amount of rows for a job / in total.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Ok, I understand.