Finding the last command issued by a blocking process

Question

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?

Answer 1

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 :

• Understanding and resolving SQL Server blocking problems
• Different Status of a SPID in SQL Server and What do they mean
• Locking in Microsoft SQL Server (Part 4 – How to detect blocking)

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

Answer 2

Using the SPID of the sleeping process:

DBCC INPUTBUFFER (<spid>)

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.