Sql-server – Finding the last command issued by a blocking process

lockingsql server

I've developed an interface from ERP SAP Business One to warehouse management, and am facing problems concerning database locks. My program includes direct write operations into the ERP database (a user defined field is filled during asynchronous event processing after creating or changing a sales order). It is suspected to be responsible for blocking situations occurring quite often each day.

When a blocking situation occurs, sp_who2 gives me information about blocking and blocked processes, every time pointing to an spid used by the BPM management software as the "root blocking process". The status of this spid is always shown as "sleeping".

  1. Can a "sleeping" process cause a database lock for other processes, e.g. when transactions weren't committed?

  2. How can I find out the last command of the sleeping process?

Best Answer

2) How can I find out the last command of the sleeping process?

You can use Adam Machanic's sp_whoisActive tool to find the lead blocker.

You can also use DMV's (starting sql 2005 and up) to find out what the spid was executing :

-- You can use "most_recent_sql_handle" in sys.dm_exec_connections to see the last statement that was executed.    
SELECT  c.session_id, t.text,
            QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
            + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
            c.connect_time,
            s.last_request_start_time,
            s.last_request_end_time,
            s.status
    FROM    sys.dm_exec_connections c
    JOIN    sys.dm_exec_sessions s
            ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
    WHERE   c.session_id = -- spid for the spid involved in blocking

-- for any OPEN Transactions, you can use `sys.dm_tran_session_transactions` and `sys.dm_tran_active_transactions`

SELECT  st.transaction_id,
        at.name,
        at.transaction_begin_time,
        at.transaction_state,
        at.transaction_status
FROM    sys.dm_tran_session_transactions st
JOIN    sys.dm_tran_active_transactions at
        ON st.transaction_id = at.transaction_id
 WHERE   st.session_id = -- spid for the spid involved in blocking

Refer to :

Alternatively, you can use a mix of Event Notification or Profiler with Blocked process report to detect blocking on your database server.

Related Question