Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, which I guess is not a problem by itself.
My question is: How do I retrieve what else had already locked the same row or table that made it unavailable to this failed update?
I am able to see all the exceptions related to Error: 1222 through traces but it is not clear to me what made it fail.
Is there a way to retrieve that or do I only have to continuously watch through Activity Monitor? Since it doesn't deadlock, everything happens pretty quick there and hard to see it live.
I need to find that by using the existing traces and any information that sql server 2008 R2 keeps.