Is there a fix for the "Remove Agent History" bug?

Question

Is there a fix for the "Remove Agent History" bug?

webrunner

SSC-Dedicated

Points: 31718
More actions
March 15, 2012 at 2:25 pm

#270112

Hi,
It looks like SQL Server 2008 R2 Enterprise Edition still has the bug documented at the link below, whereby trying to change the "Remove Agent History" settings for SQL Server Agent doesn't stick. When you go back in to check the setting, it is back to 4 Weeks (grayed out) and the checkbox unchecked.
https://connect.microsoft.com/SQLServer/feedback/details/485232/kjtap-sap-sql-server-agent-remove-agent-history-cannot-be-activated
Does anyone know if this has been fixed? The version of SQL we have is:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Thanks in advance for any help. Let me know if any further information is needed to describe the issue.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 1

This 'issue' has been around since 2005. That option is really just a real-time cleanup option as mentioned.

I haven't worried about setting that on my systems, because I use the History Cleanup task in maintenance plans to take care of the history. If you don't like maintenance plans, you can always create your own job to execute the history cleanup procedure.

That way, you never have to worry about that setting - and you have full control over when and how that is managed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

I second everything Jeffrey said.

msdb.dbo.sp_purge_jobhistory is the proc you want. I run it on all my instances regularly via a SQL Agent job.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

webrunner SSC-Dedicated Points: 31718 More actions · Answer 3

OK, thanks for this information. I will use either the History Cleanup task or the msdb.dbo.sp_purge_jobhistory procedure to handle this option. Just to confirm: either of those will override the apparent 4-week limit that appears in the nonfunctional Remove Agent History setting form, is that correct?

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

My guess is the "4-week limit" is a UI limitation. The proc is not limited in that way. Not sure about the MP task.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

webrunner SSC-Dedicated Points: 31718 More actions · Answer 5

opc.three (3/16/2012)
My guess is the "4-week limit" is a UI limitation. The proc is not limited in that way. Not sure about the MP task.

OK, thanks again. I will test it out to see.

Best regards,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

maryanne_e_2000 SSC Veteran Points: 252 More actions · Answer 6

Does this same stored procedure (msdb.dbo.sp_purge_jobhistory) also clean up SQL Server Reporting Services (SSRS) subscriptions managed by the SQL Server Agent Jobs?

Please see attached snapshot for more information on what I'm pertaining to.

Thank you.

Best Regards,

Mary Anne

maryanne_e_2000 SSC Veteran Points: 252 More actions · Answer 7

Does this same stored procedure (msdb.dbo.sp_purge_jobhistory) also clean up SQL Server Reporting Services (SSRS) subscriptions managed by the SQL Server Agent Jobs?

Please see attached snapshot for more information on what I'm pertaining to.

Thank you.

Best Regards,

Mary Anne

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

maryanne_e_2000 (12/2/2014)
Does this same stored procedure (msdb.dbo.sp_purge_jobhistory) also clean up SQL Server Reporting Services (SSRS) subscriptions managed by the SQL Server Agent Jobs?
Please see attached snapshot for more information on what I'm pertaining to.
Thank you.
Best Regards,
Mary Anne

Yes, indeed it will. Those jobs, while created by an SSRS subscription, are still just plain old Agent jobs with history rows subject to the purge-proc.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

maryanne_e_2000 SSC Veteran Points: 252 More actions · Answer 9

Thank you for your reply Orlando. So all I need to do is create a job agent that has a step to call the msdb.dbo.sp_purge_jobhistory passing in the frequency I want for cleanup and then setup that job for like a weekly cleanup schedule and that should do it?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

That should do it. I roll a job to all my instances that does just that, as a matter of standard operating procedure. Sometimes I will need to add steps to it on some instances to delete more history for specific jobs depending on how often those jobs run, e.g. for jobs that run every minute I might purge history older than a couple days whereas by default in step 1 of the job I might delete history for all jobs older than 60 days.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato