How to know what job step is currently running (SQL Server Agent)

Question

Re: SQL Server Agent

I'd like to write a script that sends specific information on job steps.

I know how to query from system tables (master.sys.sysprocesses) to find out what the job is that's currently running. The script executes in a job step and sends the name of the job in a notification to my inbox when a certain event occurs. But how do I know what step is running? Would job steps also register in the master.sys.sysprocesses table during execution?

I'd like to have the script execute during the step and send information via sp_send_dbmail to my inbox if certain errors or warnings occur - as I find the error notification service to be a little shortcoming.

Thanks

 

PS: this is running on a SQL 2008 server instance

Answer 1

Hi Experts,

 

please share the script for which step is currently running in SQL Job.

 

Thanks in advance.

 

cheers,

Zaim Raza.

Answer 2

If you have are looking at sysprocesses then the job step is at the end of the program_name string:

SQLAgent - TSQL JobStep (Job 0x71BF1A155AABC04AA06CFBE4BC4F2C1B : Step 1)

                                          

---

Regards, Matt Bowler MCITP, http://mattsql.wordpress.com/

Answer 3

Hi,

Its an old post but found it randomly while searching for one of my issue.

You use below command to get details like which step is currently running and wats last execution time and many other things.

exec

msdb.dbo.sp_help_job

@execution_status = 1

Thanks!

---

VVinayPrasad

Answer 4

Hi,

The following link may be useful.

http://stackoverflow.com/questions/200195/how-can-i-determine-the-status-of-a-job

If you want to send mail when a job fails then follow the below steps.

1. In job step set advanced properties OnFailure 'Go to the next step'

2. Create a new step

     name ->'send mail on error'

     Type ->Transact SQL script(T-SQL)

     Database ->master

     Command -> msdb..sp_notify_operator  @name='emailname',@profile_name='profile name',@subject='the job failed',@body='error message'
go

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you