How to capture information of any jobs that are hanging?

[DBFan]

How to capture information of any jobs that are hanging( for example, that are running for more than 6 hours) and send an alert in the email with job name and duration it has been running?

Thanks,

[MAK]

http://www.databasejournal.com/featu...le.php/3500276

[DBFan]

Thank you, MAK.

[DBFan]

Hi MAK,

This script is working fine except that is not capturing the jobs that are hanging in 'Performing completion tasks' state. Do you know how to capture the jobs in the above state and are hanging in the same state for ever?

Thanks,

[rmiao]

Tried msdb..sp_help_job? Check jobs with @execution_status = 7.

[DBFan]

But how do we incorporate this in the above script so that the alert will pic-up the jobs in this status as well?

Thanks,

[rmiao]

You can send sql mail with job name if you get non-null result.

[DBFan]

can you please explain me how to do it?

[rmiao]

Try something like following:

master..xp_sendmail @query = 'msdb..sp_help_job @execution_status = 7 ...' ...

[DBFan]

Hi MAK, Need your help here!!! Please let me know how to capture and send an alert with the condition mentioned above in your original script preferably.

Thanks in advance.

[DBFan]

Gurus!

Anyone to help me??

Thanks,