Accessing SQL Server Agent Data

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


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.


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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux 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 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

Comments For This Article

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

 Very Good Article


Thursday, May 5, 2016 - 4:40:58 PM - Greg Robidoux Back To Top (41409)

Hi Harsha,

take a look at this info:



Thursday, May 5, 2016 - 3:06:43 PM - harsha Back To Top (41407)

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 (40272)

Hi Syed,

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


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

 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 (37328)


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

Brendan, what version of SQL Server are you using?

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

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 (37308)

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 (37286)

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 (36477)

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).

get free sql tips
agree to terms