By: Greg Robidoux | Last Updated: 2007-06-20 | Comments (4) | SQL Server Agent
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?
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.
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
This query returns the JobHistoryMaxRowsPerJob
To update JobHistoryMaxRows value
To update JobHistoryMaxRowsPerJob value
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 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
Last Updated: 2007-06-20
About the author
View all my tips