SQL SERVER – Retrieve Information of SQL Server Agent Jobs

sp_help_job returns information about jobs that are used by SQL Server Agent 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.


SQL SERVER - Retrieve Information of SQL Server Agent Jobs sp_help_job-800x249

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server 2016. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

Here are few of the related blog posts.

Unable to start SQL ServerAgent – Failed to Initialize SQL Agent log
If you are unable to start the SQL AgentLog here is a good blog post which discusses about it.

SQLServerAgent is not currently running so it cannot be notified of this action
Getting error and finding the cause is something which I like. And off late over the blogs I have shared a number of such incidents that have helped me figure out stuffs all by myself. Of course, I do my research and more often than not make things complicated because there are a number of resolutions that I find.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Server Agent, SQL Stored Procedure
Previous Post
SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio
Next Post
SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

Related Posts

11 Comments. Leave new

  • 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

  • nice tip

  • Hi, I’m a newbie and
    I need a sql server agent query that will give me
    job name, owner name, etc….sql 2005. Do you have one?

  • Hi,

    Check the msdb.dbo.sysjobs table.

    Pinal Dave

  • 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
    ServerMSSQL.1MSSQLLOGERRORLOG’. Operating system error = 3(The system
    cannot find the path specified.).

    Please some one help me..

  • 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?

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

  • thanks u ..

  • 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!


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


  • Ashish M. Nasre
    November 20, 2015 7:26 pm

    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 ReplyCancel reply

Exit mobile version