Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Please help.

share|improve this question

migrated from stackoverflow.com Oct 25 '13 at 7:21

This question came from our site for professional and enthusiast programmers.

1  
Run SQL profiler. Note time when timeout happens. Look at the profiler log to see what else was running at exactly the same time. If you don't have any long running locks, you should be able to identify it pretty quickly. –  zespri Oct 25 '13 at 0:54
add comment

1 Answer 1

I recommend zespri's approach. Use profiler to find the query that is taking an inordinate amount of time.

If you've somehow had no joy with that, you can try the following query which will show a process with a particularly large waittime value.

It filters out any system processes and SQL which has contains WAITFOR DELAY '00:00:05' ... etc

SELECT * 
FROM master.dbo.sysprocesses 
WHERE spid > 49 
AND (blocked <> 0 OR (waittype <> 0x0000 AND lastwaittype <> 'WAITFOR'))

You can then dig a little deeper with the spid against the row that looks like it is causing the problem and inspect the SQL code that is running/locking:

DECLARE @Handle varbinary(50), @Spid int = <your spid value/>
SELECT @Handle = sql_handle from master.dbo.sysprocesses where spid = @spid
DBCC INPUTBUFFER (@spid)
SELECT * FROM master.sys.fn_get_sql(@Handle)

And the resulting output will look like this:

EventType      Parameters EventInfo
-------------- ---------- ------------------------------------------------------------
Language Event 0          SELECT GETDATE()

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbid   objectid    number encrypted text
------ ----------- ------ --------- ---------------------------------------------------
13     NULL        NULL   0         SELECT GETDATE()

This should help you narrow down the problem query.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.