Accessing SQL Server Agent Data

By: Greg Robidoux   |   Comments (11)   |   Related: > SQL Server Agent

Problem

SQL Server Agent has a lot of great information stored in the system tables in the msdb database and the GUI provides a nice way of retrieving and viewing the data, but it does not always give you the data the way you would like it displayed.  From a high level it is nice to see the current status of all jobs and the last run status of the jobs, but what if you want to see additional information or the data displayed in a different format.  On a one by one basis you can get this information from the GUI, but it is not very easy to correlate this data across jobs or even across different time periods.

Solution

As like most things in SQL Server, the data used for both system and user applications is stored in tables and is accessible via queries or stored procedures.  The data used for SQL Server Agent is just like most other processes where all the data is stored in tables and accessible. 

The following shows a list of the different system tables, views and stored procedures that are used to store and display data about SQL Server Agent jobs.  All of these objects are stored in the MSDB database.

SQL Server Agent System Tables

• dbo.sysjobactivity
  • stores data about job activity
• dbo.sysjobhistory
  • stores data for all historical runs of all jobs
• dbo.sysjobs
  • stores data about each job
• dbo.sysjobschedules
  • stores job schedule information
• dbo.sysjobservers
  • stores server information related to a job
• dbo.sysjobsteps
  • stores specific job step information
• dbo.sysjobstepslogs
  • stores specific job step log information for each run if this is enabled.

SQL Server Agent System Views

• dbo.sysjobs_view
  • this is a system stored procedure that provides data about all of the jobs.

SQL Server Agent System Stored Procedures

• sp_help_job
  • this returns info about the job. 
  • If no parameters are used info is returned for all jobs. 
  • If a specific job_id is passed it gives you job info, job step info, schedule info and last run info.
• sp_help_jobactivity
  • this returns info about the status of the job run. 
  • If no parameters are used info is returned for all jobs.
• sp_help_jobcount
  • this gives you a count of how many jobs a schedule is tied to. 
  • This requires either @schedule_id or @schedule_name to be passed as a parameter.
• sp_help_jobhistory 
  • this returns all history info for all of the job runs. 
  • If no parameters are used info is returned for all jobs.
  • If you also use parameter @mode = N'FULL' this provides additional info about each job step.
• sp_help_jobs_in_schedule
  • this gives you a list of the jobs that are tied to a schedule. 
  • This requires either @schedule_id or @schedule_name to be passed as a parameter.
• sp_help_jobschedule
  • this provides jobs schedule information for a particular job. 
  • This requires either @job_id or @job_name to be passed.
• sp_help_jobserver 
  • this provides information about a specific server tied to a job. 
  • This requires either @job_id or @job_name to be passed.
• sp_help_jobstep
  • this provides information about the job steps for a specific job. 
  • This requires either @job_id or @job_name to be passed.
• sp_help_jobsteplog
  • this returns information about a specific job step log. 
  • This requires either @job_id or @job_name to be passed.
• sp_get_composite_job_info
  • this returns combined data for all jobs in the system.
  • If no parameters are used info is returned for all jobs.

Summary

Now that you have this additional information about where SQL Server Agent data is stored as well as how to access the data, begin building your own processes to gather data about your job runs, scheduling and job information.

Next Steps

• Take the time to investigate each of these system tables, views and stored procedures.  Having the ability to get to this data without having to rely on the GUI allows you much more control over SQL Server.
• Check out this page for a complete list of objects related to SQL Server Agent
• Take a look at these other related SQL Server Agent tips.

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips