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





Comments For This Article




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

 Very Good Article

 


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

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

-Greg


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)

2012


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



download


Recommended Reading

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Queries to inventory your SQL Server Agent Jobs

SQL Server Agent Job Schedule Reporting





get free sql tips
agree to terms


Learn more about SQL Server tools