Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Jun 21 '13 at 14:08

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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