CODEWITHSUNDEEP
sqlsql-serverlogginglocking
PKay
PKay

Reputation: 433

sql server error - Lock Request Timeout exceeded

I get an error 'lock request time out exceeded' when a select query is run.

I know when this error will come. Some transaction should be locking the table.

But I need to know how to find that.

Please note that I cannot check it live when it is getting locked. It is like it happened in the past but I need to know what was the other transaction/query that was blocking it that time.

Simply put I want these events to be logged in sql error logs when this happens so that I can see later and find who was the long running query blocking it.

Any idea how to get it logged. I tried to set the trace flags and check. DBCC TRACEON (1204,1222,-1) But I couldn't find anything logged related to this. Thanks in advance.

Upvotes: 3

Views: 7573

Answers (1)

u07ch
u07ch

Reputation: 13702

What version of sql server are you running on? If you are on a version prior to SQL 2008 then you will need to work with our IT people to analyse it in live as it happens.

If 2008 you can retrospectively view dead lock events as they are exposed in management views. Its stored as XML but you can break it out easily enough...

Select 
    DLEvent.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
From
    (
        select CAST(target_data as xml) as DLData
        from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s 
        on s.address = st.event_session_address
        where name = 'system_health'
    ) AS DLData

CROSS APPLY DLData.nodes ('//RingBufferTarget/event') AS DLEvent (XEvent)

Where DLEvent.XEvent.value('@name', 'varchar(max)') = 'xml_deadlock_report'

Upvotes: 1

Related Questions