dwilliscp (1/16/2013)
Is there any SQL that can be added to a Stored Proc to track if there was any blocking of its SQL, or for that matter explain the example below?I am tasked with explaining, and if possible fixing, jobs that sometimes run more than twice their normal run times. A good example is a program that almost always runs in less than one min, but this morning it took 4 1/2 hours to run. This created problems since our data was not fully loaded when folks started using the application.
Thanks
No. There is no SQL that can be added to a Stored Proc to track if there was any blocking of its SQL.
But you can create another job to periodically check for the blocking and send you alert if there is any blocking.
You can start here:select * from sys.dm_exec_requests where blocking_session > 0
Note that your job might have taken 4.5 hrs due to some other reason also (not necessary due to blocking).
There was no deadlock. Deadlock would fail the job.