0

I was hoping i could get some help on how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail alert when the run duration exceeds 30 minutes.

Would it be easier to add this step in the job itself? Are there any available methods in the SQL Agent GUI or do i have to create a new job? I figured creating a new job is less likely as i would have to query the sysjobhistory in msdb; The value is only updated once the job finishes so that doesn't help...I need it to check the real time duration of 1 specific agent job as it's running... Specifically because it happens that the job runs into a deadlock ( That's no longer an issue now), so the job just stays stuck on the table it's locked on, and i only get the notification from the enduser that the report doesn't return results :S

samalkobi
  • 77
  • 1
  • 10

1 Answers1

0

The best method outside of 3rd party monitoring software is to create a high-frequency SQL Agent Job that runs a query on active sessions (returned by something like sp_who) for the duration of spids. This way you can have this monitoring job email you whenever a spid goes over a threshold. Alternatively you could have it compare the current runtime vs a calculated average runtime gleaned from the sys.jobhistory table.

Razzle Dazzle
  • 481
  • 2
  • 8
  • 20
  • Hmm, i see what you mean, but isn't a SPID just a randomly generated number assigned to a given task? In other words, the SPID for the job would have a different SPID on every run...? – samalkobi May 12 '15 at 14:14
  • Correct, why is that a problem though? Using a combination of Sysjobs in msdb and sysprocesses you should be able to identify what the spid is based on job name. – Razzle Dazzle May 12 '15 at 15:02