SQL SERVER – Retrieve Information of SQL Server Agent Jobs « SQL Server Journey with SQL Authority

« SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length »

SQL SERVER – Retrieve Information of SQL Server Agent Jobs

May 31, 2007 by pinaldave

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.
EXEC MSDB.DBO.sp_HELP_JOB

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Server Agent | 8 Comments

8 Responses

on June 8, 2007 at 2:40 pm | Reply Wilson

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…

Regards

Wilson Gunanithi . J
on July 7, 2009 at 11:10 pm | Reply leoguy

nice tip
on December 30, 2009 at 1:15 am | Reply amc

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?
on December 30, 2009 at 11:06 pm | Reply Pinal Dave

Hi,

Check the msdb.dbo.sysjobs table.

Regards,
Pinal Dave
on April 7, 2010 at 3:07 pm | Reply sahasra

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..
on June 17, 2010 at 11:28 am | Reply Sasikala

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?
on December 13, 2010 at 12:31 pm | Reply Dinkar

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.
on February 25, 2011 at 10:40 am | Reply zithendra

thanks u ..