SQL SERVER – Retrieve Information of SQL Server Agent Jobs

sp_help_job returns information about jobs that are used by SQLServerAgent service to perform automated activities in SQL Server. When executed sp_help_job procedure with no parameters to return the information for all of the jobs currently defined in the msdb database.


Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

11 thoughts on “SQL SERVER – Retrieve Information of SQL Server Agent Jobs

  1. Sorry Sir,

    I dont have any idea about the SQL Server Agent Jobs… So please expain me what it is? and where it is used ?
    with example…


    Wilson Gunanithi . J


  2. Hi ,

    My job is failing every day .
    The error message in sql server logs is :

    initerrlog: Could not open error log file ‘R:\Microsoft SQL
    Server\MSSQL.1\MSSQL\LOG\ERRORLOG’. Operating system error = 3(The system
    cannot find the path specified.).

    Please some one help me..


  3. Hi Pinal,

    Nice tip. Thanks.

    sp_help_job is giving current_execution_status, which is giving the current execution status of the job. And this stored procedure internally uses xp_sqlagent_enum_jobs to get the current execution status.

    I would want to extract the execution status or currently running jobs from tables. Am not allowed to use any procedures. So could you please help me to get the execution status from table? or what is happening inside of the procedure xp_sqlagent_enum_jobs?


  4. Hi Pinal,
    I am new in SQL Server so please bear with me.

    I have a question regarding proxy a/c permissions with SQL Server Agent job:
    i am logged in as user1 with sysadmin privillages, created a job with owner user2 who only have public permissions. Used same a/c to create proxy a/c which i used in user2’s agent job.
    Agent job is just fetching new rows from one server and transferring them into another.
    i was thinking that wont be sucessful because user2 didnt have any sysadmin or agentjob roles(just had public privilage). But job got executed sucessfully and it did transfer rows to other server as well.

    My query is that why it happened?
    And could you please tell me what minimum permissions i need to give to login to run a multiserver job.

    Thanks in advance Pinal.


  5. Hi Pinal!

    Love your blog! I hope this is the correct place to ask this, but is it possible to retrieve information about a job(s) that is currently running? My problem is this: I have 4 or 5 ETL and Backup jobs which execute daily and which also need to be reported on every morning (run time, run status, etc). So, in a nutshell, I need to be able to see information such as:
    – job 1 currently running and is on step 5
    – job 2 succeeded
    – etc…

    Is this even possible or would I need to do something like check to see if the last run date != current date either in T-SQL or manually?

    Thank you!



  6. Hello Pinal,.
    How can we check the JOBS are using against which database.. there are 50 jobs in my instance and want to move the jobs for specific database.
    And Is there any way to find out the description of that job.. (which Sp its calling or t-sql statement etc)



  7. We are using sql server 2012 and soon we will migrate to sql server 2014 but why old one transaction handling for agent job.we are using @@error and goto statement for transaction handling in job script.
    why we are not using latest one even @@error active till next statement only after error statement.

    Please suggest me on the same.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s