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?


2 Answers 2


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,
    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,
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.


Using the SPID of the sleeping process:


This will give you the last input string that was executed by that session.

Now technically, that's not necessarily the last SQL Statement executed, because if the input command did something like call a stored procedure, then there could be a lot of SQL Statements inside of the one input command. So, if you need the last/current SQL Statement instead, then let me know as there's a slightly more complicated way to get that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.