Accessing SQL Server Agent Data

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 has a lot of great information stored in the system tables 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.  All of these objects are specific to SQL Server 2005, but some of these objects also exist in SQL Server 2000.  Check out books online for specific tables and stored procedures that relate to SQL Server 2000.

System Tables

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

Views

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

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.
• Take a look at these other related SQL Server Agent tips.
• Stay tuned for additional tips related to SQL Server Agent

Related Tips: More | Become a paid author

Last Update: 5/15/2007

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

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 free performance monitoring tool from Idera! SQL Monitor – For database professionals who need results on Day One. Try it online. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Are you waiting on SQL Server? Learn about these DMV's.