Retaining SQL Server Job History
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?
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:
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:
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.
- 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.
About the author
View all my tips
Article Last Updated: 2007-04-25