Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Accessing SQL Server Agent Data


By:   |   Updated: 2019-09-06   |   Comments (11)   |   Related: More > 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

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


Last Updated: 2019-09-06


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, December 31, 2017 - 11:56:24 PM - Krishna Back To Top

 Very Good Article

 


Thursday, May 05, 2016 - 4:40:58 PM - Greg Robidoux Back To Top

Hi Harsha,

take a look at this info: https://msdn.microsoft.com/en-us/library/ms188019.aspx

Thanks
Greg

 


Thursday, May 05, 2016 - 3:06:43 PM - harsha Back To Top

Where does the SQL Server Agent Proxie information is stored. I am trying to retrive the principals used for a proxy as well as the subsystems selected for each proxy. Could you please tell me where i can get that info? 

 


Thursday, December 17, 2015 - 2:18:43 PM - Greg Robidoux Back To Top

Hi Syed,

I have not done this, so not sure if this can be done or not.

-Greg


Thursday, December 17, 2015 - 12:00:17 PM - Syed Back To Top

 Hello Greg,

If we run sql agent job using a proxy account. And want to know who is the user that ran the job manually from sql agent, is there any way we can gather this information from sys tables.

If not do you suggest any way to capture this information. This above job is a simple SSIS and i have logging in package via sys variables but capturse only proxy account info not the actual user who ran it manually.

Any help greatly appreciated.

 


Friday, May 29, 2015 - 11:35:12 AM - Brendan Back To Top

2012


Friday, May 29, 2015 - 9:03:08 AM - Greg Robidoux Back To Top

Brendan, what version of SQL Server are you using?


Friday, May 29, 2015 - 8:39:21 AM - Brendan Back To Top

Hi Greg.  Thanks for the quick response.  I was thinking of the information stored with the step and visible through the Configuration tab.  As you know, if you have the "Job Step Properties" window open, and the step is of type "SQL Server Integration Services Packages," there are two tabs.  One is "Package" and it shows the package source, server, authentication method, and package location and name.  The other is "Configuration" and it has three sub-tabs - Parameters, Connection Managers, and Advanced.  The Parameters sub-tab has a list of Parameter names and values.  That's what I was looking for.


Thursday, May 28, 2015 - 4:51:22 PM - Greg Robidoux Back To Top

Hi Brendan, not exactly sure what you are looking for.  You could search sysjobsteps to see the SQL code in the job step.


Wednesday, May 27, 2015 - 11:15:11 AM - Brendan Back To Top

Is there a way to seach for job step parameters (names and values).  I thought there might be something like sysjobstepparams, but no.


Tuesday, March 10, 2015 - 7:17:00 AM - Deadly-Bagel Back To Top

Was looking for a way to update countless SQL jobs to run half an hour later, while this put me on the right track the appropriate database to modify is dbo.sysschedules which contains all the schedule data for SQL Agent jobs (dbo.sysjobschedules only gives the next run date and time).


Learn more about SQL Server tools