September 29, 2011 at 7:24 am
Thanks in advance for any help on this!!
I set up a performance alert to fire when the counter [Lock Wait Time (ms)] for the instance [_Total] rises above a certain threshold (for testing I set it to just 50). I then fired off 2 SPIDs to create a blocking condition. The alert WILL NOT fire until after I release the locks. When I finally get the alert email it will say something like this:
DESCRIPTION:The SQL Server performance counter 'Lock Wait Time (ms)' (instance '_Total') of object 'SQLServer:Locks' is now above the threshold of 50.00 (the current value is 8757.00).
Does anyone know why the alert will not fire immediately after the threshold is crossed? Why does it wait until after I release the locks?
Thanks!
October 4, 2011 at 3:25 pm
Ok, I did some more research on this and it looks like the counter SQLServer:Locks/Lock Wait Time (ms) will not actually record until the locking event ends.
Here is my problem. We have an application that apparently does not set a LOCK_TIMEOUT. Occasionally, I have seen multiple user processes block each other indefinitely (i.e. the processes just hang).
I think the ideal situation is for the dba to be alerted and get the problem corrected before the user calls in. In this case I don't know how that would be possible.
I know this is not the long-term solution (which would be changing the application), but it is the immediate need.
Any ideas?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply