Missing SQL Server Agent History

By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

Problem
SQL Server Agent is a useful tool to allow you to schedule reoccurring jobs to run on your server.  Most of these jobs are usually maintenance type jobs or data loads, but this tool can be used to run any T-SQL code or even execute programs external to SQL Server.  In addition to the ability to schedule jobs to run, SQL Server Agent also gives you the ability to see historical run information for the jobs as well as for each step within the jobs.  This information is very helpful to have on hand for knowing if the job succeeded as well as how long the job took to run. This way the next time you need to run one of these jobs you have an idea of how long it will take to complete.  If you have jobs that run on a frequent basis or if you have a lot of jobs that are scheduled you may notice that there is not much historical information stored when you view job history, so what can be done to keep more historical run information?

Solution
In SQL Server 2000 and 2005 the SQL Server Agent has a default setting for the maximum number of history records to store for all job runs as well as the maximum number of history rows to store per job.  As you can see in the next two screen shots the "Job history log" default settings are to store a maximum of 1000 rows for all job history and store a maximum of 100 rows per job.  Once these numbers are exceeded SQL Server will remove the older records to keep a maximum of 1000 rows.  Therefore if you have a few jobs that execute very frequently and other jobs that are run only monthly or quarterly, SQL Server will flush out the older data and therefore you will not be able to see the older historical run data.

SQL 2000

SQL 2005

These two values can be changed to any value that makes sense for you environment. The maximum number of rows per job is probably a sufficient number, but the overall job history rows is probably too small for most environments and this number should probably be increased to a higher value.  Again this depends on how many jobs you have and also how frequently the jobs run.

In addition, to setting either the Max Job History or the Max Job History Per Job to higher numbers you also have the ability to turn these off completely and not have SQL Server enforce any limitation. This may sound like a good thing, but if you have a job that runs every minute this will consume a lot of space for all of these job runs, so you need to be aware of how often your jobs are running and also how much job history you really want to keep.  Also, with no limitations the history table will grow forever.

A couple of other options on this screen are with SQL Server 2000 you have the ability to clear the entire job log from this screen, so you can clear out all historical run data.  With SQL Server 2005 you now have the ability to set a value for how old you want to keep historical data.  This will allow you to trim and manage your SQL Server Agent history log.

 

Where is this data stored?
As you can see this is pretty straight forward to make these adjustments using the GUI.  Often though DBAs need to do this on multiple servers and creating and running T-SQL code on all of these machines is often a better approach. This allows you to store your scripts and have a record of what and when things were run.  Most of the components that SQL Server uses are stored in one of the system databases and because of this changes can be made either using the management tools or by issuing T-SQL statements.  This is the case for most system values, but this is not the case for the job history settings. 

If your run profiler and then make these changes via the GUI you can catch the commands that SQL Server is issuing to read and update these values.  You will notice that the data is not stored in a table, but is stored in the registry.  SQL Server uses two extended stored procedures xp_instance_regread and xp_instance_regwrite to read and write these values within SQL Server.

Here is the command that is used to read the JobHistoryMaxRows value:

Here is the command that is used to read the  JobHistoryMaxRowPerJob value:

Here is the command that is used to update the JobHistoryMaxRowPerJob value:

Here is the command that is used to update the JobHistoryMaxRows value:

As you may have noticed there are just two values JobHistoryMaxRows  and JobHistoryMaxRowsPerJob.   When you unselect the "Limit size of job history log", SQL Server will update JobHistoryMaxRows JobHistoryMaxRowsPerJob in the registry.

For SQL 2000 the JobHistoryMaxRows is set 4294967295 and JobHistoryMaxRowsPerJob is left unchanged.

For SQL 2005 the JobHistoryMaxRows is set 4294967295 and JobHistoryMaxRowsPerJob is changed to 0.

When the "Limit size of job history log" is selected again these values revert back to the default values.

 

Using the XPs
To query the values using the extended stored procedures you can issue the following queries.

This query returns the JobHistoryMaxRows

DECLARE @jobhistory_max_rows INT EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', @jobhistory_max_rows OUTPUT PRINT @jobhistory_max_rows

This query returns the JobHistoryMaxRowsPerJob

DECLARE @jobhistory_max_rows_per_job INT EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', @jobhistory_max_rows_per_job OUTPUT PRINT @jobhistory_max_rows_per_job

To update JobHistoryMaxRows value

EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRows', N'REG_DWORD', 100000

To update JobHistoryMaxRowsPerJob value

EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'JobHistoryMaxRowsPerJob', N'REG_DWORD', 100

Although this is pretty straightforward I would recommend using the GUI to make these changes unless you are totally comfortable with using the extended stored procedures.

Next Steps

• Next time you notice that you are missing job history, check these settings to see if you have the default values still set
• Look at using the new feature in SQL 2005 to remove older data based on a date setting
• Although there is not a simple way to change these values on all systems it is possible to use the extended stored procedures to do the update for you.
• Review these other SQL Server Agent tips

Related Tips: More | Become a paid author

Last Update: 6/20/2007

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! SQL Monitor, server monitoring so easy, your boss could do it. Try it online. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Get SQL Server Tips Straight from Kevin Kline. Join the over million SQL Server Professionals who get their issues resolved daily. Are you waiting on SQL Server? Learn about these DMV's.