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'm really having trouble tracking down some blocking we are experiencing.

The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'.

When I view the Top Transactions by Blocked Transactions Count report, the Blocking SQL Statement is '--'.

I've performed a trace on the SQL and when the blocking happens tracing the root blocking SPID but it hasn't really led me anywhere. The last trace statement is the same as the sqltext above 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'.

I've checked all the related SPROCs I can find to make sure they have TRY/CATCH BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements (we use SPROCs for everything so there are no standalone statements being ran). This issue just started happening over the last 24 hours and no one is claiming to have made any changes to the system.

Solution. One of our seldomly used SPROCs had an error with an insert (number of columns didn't match), but we are still confused on what exactly was happening.

When looking at all the trace information, the EXEC statement for this SPROC was listed at times, but NEVER just before the BLOCK happened on the blockking SPID. It seemed that when it starting blocking, the trace didn't record the execution of it (or any of the statements within it either). However there are other times were the trace did record it's execution and no blocking occurred.

The SPROC error report came from a user, and I was able to find multiple EXEC statements in traces and run them in SSMS. No time when I ran them did we have any blocking occur or did they hang. They ran as expected (the catch block fired and rolled back the transaction after the error). After resolving the fixing the SPROC, we have not seen the issue again.

share|improve this question
    
I'm assuming the host name of the blocking SPID didn't help at all? –  Jon Seigel May 7 '13 at 19:41
    
No, it's just the IP of one of our webservers... We have another idea to change every SQL login for each SPROC call during the login/registration process (which we feel is where the error is occuring) to a separate username which may help us isolate which SPROC could be causing the blocking. –  Brad May 7 '13 at 19:53
    
TRY/CATCH will not catch compilation errors and a mismatched column insert would be such a compilation error. This would also never trigger many of the XX:Completed profiler events. –  Remus Rusanu May 9 '13 at 11:58
    
It's actually not a compilation error in this case because the genius developer used INSERT INTO [table] SELECT * from [othertable] and it wasn't caught in peer. I ran the SPROC on Development 1000 times from ColdFusion in 3 simultaneous sessions and it never left open a transaction like it was on Production. –  Brad May 9 '13 at 13:07
add comment

3 Answers

up vote 2 down vote accepted

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.

So, you need to use SET XACT_ABORT ON or add some client rollback code

See SQL Server Transaction Timeout for all the gory details

share|improve this answer
    
All our SPROCs contain TRY/CATCH blocks and BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements, ROLLBACK being in the CATCH. Would XACT_ABORT still have an effect? –  Brad May 8 '13 at 18:25
    
@Brad: yes. See my link. The catch block is not hit on CommandTimeout –  gbn May 8 '13 at 19:16
    
gbn: Thanks. I'm still confused. Our connections are set to never timeout (0). So you are saying if we re-use connections, and a connection runs a SPROC with an error (that has TRY/CATCH and TRAN blocks), it can somehow never run the ROLLBACK in the CATCH block thus locking up tables and keeping a transaction open? That doesn't make sense to me. –  Brad May 9 '13 at 4:05
    
@Brad: An SPROC with an error will hit the CATCH block. I did not say otherwise or different. But my link states what happens if you have a CommandTimeout, which is different to a ConnectionTimeout. The client says "abort" and SQL Server stops processing. Ergo, the CATCH block or rollback or commit is never hit –  gbn May 9 '13 at 6:39
    
I don't think we have a CommandTimeout specified. All our stored procedures are testing using sqlstress and must perform under 1000ms at 10 users 10 iterations (at the minimum). I'm still very confused about what has happened, but I'm updating the question with what we found was the problem. –  Brad May 9 '13 at 11:12
show 6 more comments

Have you tried using Adam Machanic's sp_whoisactive? There's an option to get the outer command to see if it really is within a proc. It could be the application is holding open a transaction instead of committing it. Try looking at DBCC OPENTRAN as well.

share|improve this answer
    
Thanks for DBCC OPENTRAN. It does tell me that the blocking PID has a open transaction, but no more details are available. sp_whoisactive returns the same information on the blocked process as I've been able to get on my own. There is still no details on what is going on other than 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' –  Brad May 7 '13 at 22:09
add comment

Use the most_recent_sql_handle in sys.dm_exec_connections to see the last statement that was executed.

SELECT  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 = 72;--your blocking spid

Also check if there is an open transactions for that spid

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 = 72;--your blocking spid
share|improve this answer
    
You could also use DBCC INPUTBUFFER(spid) to see the last SQL executed. –  Mike Fal May 7 '13 at 21:00
    
I've used all those and the last command is always what I put in my original post: SET TRANSACTION ISOLATION LEVEL READ COMMITTED. I also ran DBCC OPENTRAN and can see that there is an open transaction for the blocking PID. –  Brad May 7 '13 at 22:08
    
My first select also gives you the procedure name, if the statement is indeed part of a procedure. –  Sebastian Meine May 8 '13 at 7:05
    
I assure you we use no adhoc queries from our web servers, and when I run that first query, even without the WHERE clause I only get the named SPROC on a handful of the SQL sessions, the rest that column is NULL. –  Brad May 8 '13 at 13:53
    
I do notice that I have tons of sessions that say 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' and all of them are from ColdFusion (the main scripting used on our web servers). Perhaps ColdFusion when idle issues that statement to hold open a connection (as it's set to keep connections open). –  Brad May 8 '13 at 14:01
show 1 more 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.