Problem: I occasionally receive requests to count SQL Server Agent Job runs for the past week, month, quarter, or year. However, I notice that the msdb sysjobhistory table does not always maintain historical job run data for the requests that I receive. Job run counts are sometimes less than they should be, and the shortfall is typically most common when I have to count jobs over longer periods, jobs with many steps, or jobs that run very often.
Solution: The sysjobhistory table in the msdb database is the table that maintains historical data on SQL Server Agent job runs, such as when they run, how long they run, and whether a run is successful. This kind of information is maintained at both individual job step and overall job levels. However, there are default settings for limits on the amount of history maintained in the sysjobhistory table. Additionally, you can manually or programmatically configure the limits of how much data can be stored in the sysjobhistory table.
To generate valid historical data about SQL Server Agent Job runs, you need a working knowledge of the kind of data in the sysjobhistory table as well as how SQL Server enables you to administer limits on the amount of data stored in the sysjobhistory table. In this blog post, we will explore the columns and rows of the sysjobhistory table, discuss manual and programmatic means of setting limits on the amount of data in the sysjobhistory table, and provide code samples to help you maintain historical data about SQL Server job runs outside of the built-in msdb SQL Server Agent tables for special job run tracking requirements.
Understanding the sysjobhistory Table
The sysjobhistory table stores historical data about job runs at both the job step and overall job levels. Each run of a job is represented by a set of rows in the sysjobhistory table. A job run with a single step will have 2 rows in its set — one for its sole step and another for the overall job. In general, the row set size for a job run will be one more than the number of executed steps in the job. If a job is disabled and it is never run on demand, the job will not have any rows in the sysjobhistory table.
Here is an example query that extracts selected column values from the sysjobhistory table:
SELECT name, sysjobhistory.instance_id, sysjobhistory.job_id, sysjobhistory.step_id, sysjobhistory.step_name, sysjobhistory.sql_message_id, sysjobhistory.sql_severity, sysjobhistory.run_status, sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration FROM msdb.dbo.sysjobs LEFT JOIN msdb.dbo.sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id ORDER BY name, instance_id
The sysjobhistory table has several columns that provide information about job runs, such as the job name, step name, SQL message ID, SQL severity, run status, run date, run time, and run duration. By querying this table, you can retrieve historical data about job runs and perform various analyses.
Setting Limits on Job Run History
By default, there are limits on the amount of history maintained in the sysjobhistory table. The default limit for the number of rows per job is 100, and the default limit for the number of rows over all jobs is 1000. These limits allow the job history table to be opened quickly, but they may not be appropriate for tracking jobs that run often and/or have many steps or which need to be tracked over several weeks or even months.
You can manually change the log history retained in terms of the number of rows or the length in time from today. To do this, right-click SQL Server Agent in Object Explorer, select Properties, and go to the History page. From there, you can modify the limits for the maximum job history log size (in rows) and the maximum job history rows per job.
If you prefer a programmatic approach, you can use the sp_set_sqlagent_properties stored procedure to set the maximum job history log size and the maximum job history rows per job. Additionally, you can use the sp_purge_jobhistory stored procedure to purge job history based on a specified date. This allows you to control the amount of history retained in the sysjobhistory table based on your specific requirements.
Counting Job Runs
In addition to just listing historical data for job runs, you can use the sysjobhistory table to count the number of successful and non-successful job runs. This can provide valuable information about how a SQL Server instance is being used and identify any issues that need attention.
Here is an example query that counts the number of successful and non-successful job runs for enabled, scheduled jobs:
SELECT enabled_scheduled_jobs.name, ISNULL(success_jobs.number_of_success_runs, 0) AS number_of_success_runs, ISNULL(success_jobs.total_duration_for_success_runs_secs, 0) AS total_duration_for_success_runs_secs, ISNULL(not_success_jobs.number_of_not_success_runs, 0) AS number_of_not_success_runs, ISNULL(not_success_jobs.total_duration_for_not_success_runs_secs, 0) AS total_duration_for_not_success_runs_secs FROM ( SELECT DISTINCT sysjobs.job_id, name FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id WHERE enabled = 1 ) enabled_scheduled_jobs LEFT JOIN ( SELECT sysjobhistory.job_id, COUNT(*) AS number_of_success_runs, SUM(run_duration) AS total_duration_for_success_runs_secs FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_status = 1 GROUP BY sysjobhistory.job_id ) success_jobs ON enabled_scheduled_jobs.job_id = success_jobs.job_id LEFT JOIN ( SELECT sysjobhistory.job_id, COUNT(*) AS number_of_not_success_runs, SUM(run_duration) AS total_duration_for_not_success_runs_secs FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_status != 1 GROUP BY sysjobhistory.job_id ) not_success_jobs ON enabled_scheduled_jobs.job_id = not_success_jobs.job_id
This query provides information about the number of successful and non-successful job runs, as well as the total duration for each category. By analyzing this data, you can gain insights into the performance and reliability of your SQL Server Agent jobs.
Creating an External Job History Table
If you need to store more job run history than the sysjobhistory table allows, you can create an external job history table in another database. This allows you to store as much history as you like without impacting the size of the sysjobhistory table.
Here is an example script that creates an external job history table:
CREATE TABLE for_sql_server_agent_jobs.dbo.external_job_history( [name] [sysname] NOT NULL, [step_name] [sysname] NULL, [sql_message_id] [int] NULL, [sql_severity] [int] NULL, [run_status] [int] NULL, [run_date] [int] NULL, [run_time] [int] NULL, [run_duration] [int] NULL ) ON [PRIMARY]
You can then populate this table with job run history by periodically running a script like the following:
INSERT INTO for_sql_server_agent_jobs.dbo.external_job_history SELECT name, sysjobhistory.step_name, sysjobhistory.sql_message_id, sysjobhistory.sql_severity, sysjobhistory.run_status, sysjobhistory.run_date, sysjobhistory.run_time, sysjobhistory.run_duration FROM msdb.dbo.sysjobs LEFT JOIN msdb.dbo.sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id WHERE sysjobs.enabled = 1 AND sysjobhistory.run_date IS NOT NULL ORDER BY name, instance_id, run_date, run_time
By creating and populating an external job history table, you can store and analyze job run history beyond the limits of the sysjobhistory table.
Conclusion
Tracking and counting SQL Server Agent job runs is an important task for SQL Server administrators. By understanding the sysjobhistory table and setting appropriate limits on job run history, you can accurately track and count job runs for a variety of different requirements. Additionally, by creating an external job history table, you can store and analyze job run history beyond the limits of the sysjobhistory table.
By following the guidelines and using the code samples provided in this blog post, you can effectively track and count SQL Server Agent job runs and gain valuable insights into the performance and reliability of your SQL Server environment.