Retaining SQL Server Job History

By:   |   Updated: 2007-04-25   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | > SQL Server Agent


Problem

I have a set of SQL Server Jobs (transaction log backups, notification of business rules, etc.) that run every 5 minutes just about 24 hours a day.  I seem to lose my SQL Server Job History in a few days and need this type of data to troubleshoot specific situations.  It always seems like I lose this SQL Server Job related data when I need it most.  I am trying to find the SQL Server Job that someone could have setup to delete from these tables and I only see the Jobs that my team created.  I have gone over these with a fine tooth comb and none of these delete data from the SQL Server Job related tables in the MSDB database. What exactly is deleting these entries?  Do I need to start backing up the MSDB database a few times a day to troubleshoot this issue?

Solution

That is a great question and I can understand how that information would be very valuable when troubleshooting specific issues.  To answer your question, no - you do not need to backup your MSDB database to capture this history. This is actually a configurable parameter for SQL Server Agent for both SQL Server 2000 and 2005. Let's look at both versions of SQL Server to show you how to change the Job History Log configuration.

SQL Server 2000

SQL Server 2000 - To access the SQL Server Agent Job History in Enterprise Manager, navigate to:
  • Root
  • SQL Server Instance
  • Management
  • SQL Server Agent
  • Right click on SQL Server Agent
  • Select the 'Properties' option
  • Select the 'Job System' tab

In SQL Server 2000, it is possible to permit automatic deletions based on the values for the 'Maximum job history log size (rows)' and 'Maximum job history rows per job'.  If you do not want SQL Server to automatically delete the data, uncheck the 'Limit size of job history log' check box.

SQLServer2000 JobHistory

SQL Server 2005

SQL Server 2005 - To access the SQL Server Agent Job History in Management Studio, navigate to:
  • Root
  • SQL Server Instance
  • SQL Server Agent
  • Right click on SQL Server Agent
  • Select the 'Properties' option
  • Select the 'History' page

In SQL Server 2005 the same automatic deletions based on the values for the 'Maximum job history log size (rows)' and 'Maximum job history rows per job' can be used as in SQL Server 2005.  The new option that exists relates to deleting data based on an overall duration that can be specified in days, weeks or months.  So for example, it is possible it retain 2 months or 6 weeks worth of Job History based on your needs independent of the number of rows that would be stored in the corresponding tables in the MSDB database.

SQLServer2005 JobHistory

As you can see, by changing the Job History Log configuration, you will be able to determine the amount of Job History you want to retain and have SQL Server delete the values or handle this process on your own, based on your business needs.  Just keep in mind that your MSDB database will be storing all of this data.  As such, make sure the database configurations will support the growth and validate that you have sufficient disk space to support MSDB as it grows.

Next Steps
  • If you are facing SQL Server Job entries mysteriously disappearing, this tip is the resolution.  So rest assured that a reasonable explanation is available.
  • Determine the correct configurations for the Job History on your SQL Servers and adjust the configurations appropriately.
  • Be sure to follow your change management process and to answer your next logical question, a SQL Server or SQL Server Agent restart is not needed for these configurations to be implemented.
  • Check out the additional SQL Server Agent tips on MSSQLTips.com.

     


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-04-25

Comments For This Article




Monday, November 17, 2014 - 2:55:36 PM - Irina Back To Top (35314)

Hi Jeremy,

Can I check both the options? Or it has to be one orthe other (Limit Size of the job history log or Automatically Remove Agent history)?


Thanks.

Thursday, August 21, 2008 - 1:26:57 PM - grobido Back To Top (1675)

It looks like the "Automatically Remove Agent History" is a one time run.   When you set this and click "OK" it will purge the history just this one time.  If you want this to occur on set basis you would need to setup a job and run sp_purge_jobhistory.

I am having the same issue where the date that is being generated for sp_purge_jobhistory is not correct. 

I am not totally sure how this is being generated, because the date that is being selected is all over the place.

If I select 4 days I get this date - 2008-04-21T16:24:35

If I select 4 months I get this date - 2008-08-17T16:25:06

If I select 4 weeks I get this date - 2008-07-24T16:25:26

It looks like the 4 days and 4 months are backwards.  It seems like if you want months you need to select days and if you want days you need to select months.  The only one that looks correct is the weeks.

I am running SQL Server Developer 9.0.3068

Wednesday, August 20, 2008 - 5:32:23 AM - slindsay Back To Top (1664)

Who ever knows the answer?  It is odd that this does not work through SQL Server 2005 Management Studio.

 

Tuesday, August 19, 2008 - 11:49:54 PM - sqljunkies2 Back To Top (1660)

 Hi Steve, is your question to me or the admin? 


Monday, August 4, 2008 - 12:46:32 PM - slindsay Back To Top (1557)

Hi

I'm trying to do this and when I change the history retention via Management Studio on a 2005 server it does not change. I made it retain 10 days and turned off the upper part. 

 

Is there a trick to this I am missing?

 

Steve


Tuesday, May 13, 2008 - 12:10:18 PM - sqljunkies2 Back To Top (982)

Hi,

Forgot to mention I'm using SQL 2005 (without SP though, my company didn't approve the SP deployment yet, may be this had been fixed...)


Friday, May 9, 2008 - 2:41:45 PM - admin Back To Top (966)

sqljunkies2,

Are you having an issue with SQL Server 2000 or 2005?

Thank you,
The MSSQLTips.com Team

Tuesday, April 22, 2008 - 4:13:38 PM - sqljunkies2 Back To Top (904)

Hi, thanks for the article. But when I tried to set the option "Automatically remove agent history" to 6 month(s). I assumed it will save this value/option to registry like the jobs history rows limit or per job history rows limit. I were supprised to see the checked box is clear after I click apply and reopen the properties dialog box. Out of curiosity I clicked on the "script" button to see the what script it generated and found:

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2008-04-21T15:41:54'
GO

and as of right now the date/time is around 4/22/2008 15:41, so it looks like the feature is not doing what it says it is :). This is just clean up everything until yesterday around this time (3:41pm). Instead I have to schedule a monthly task to clean up 6 months job log as

DECLARE @the_oldest_date SMALLDATETIME

SET @the_oldest_date = CONVERT(varchar(10), DATEADD(month, -6, GETDATE()), 112)

SET @the_oldest_date = DATEADD(day, -DAY(@the_oldest_date)+1, @the_oldest_date)

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date= @the_oldest_date

 

Do you think the dialog box did not do what it suppose to do?

 

Thanks!

 

 














get free sql tips
agree to terms