Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retaining SQL Server Job History


By:   |   Last Updated: 2007-04-25   |   Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

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.

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.

     


Last Updated: 2007-04-25


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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


Monday, August 04, 2008 - 12:46:32 PM - slindsay Back To Top

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

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 09, 2008 - 2:41:45 PM - admin Back To Top

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

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!

 

 


Learn more about SQL Server tools