Sql server error - lock timeout exceeded

Question

When I execute the selection request, I get the error message “Exception request blocking time”.

I know when this error will come. Some transactions must lock the table.

But I need to know how to find this.

Please note that I cannot check it live when it is blocked. This is similar to what it was in the past, but I need to know what the other transaction / request was that blocked this time.

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

Any idea how to do this. I tried setting trace flags and checking. DBCC TRACEON (1204, 122, -1) But I could not find anything related to this. Thanks in advance.

Answer 1

What version of sql server are you using? If you are using a version prior to SQL 2008, you will need to work with our IT staff to analyze it live as it happens.

If in 2008 you can retrospectively view deadlock events as they appear in management views. It is stored as XML, but you can easily break it ...

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'