Post reply

Blocking by a sleeping task.

  • June 5, 2016 at 1:09 pm

    #400241

    I have been having an interesting issue that seems to be getting worse. There have been times when our web application will issue a SQL statement doing either an update or insert but will not commit/rollback a transaction and the SPID indicates it is sleeping. I found a script to assist in finding blocking and when I run the blocking script I get the following info

    Blocking session does not have an open request and may be due to an uncommitted transaction.

    Anyone have any idea what I might look at. It seems the SQL statement causing the blocking has completed and is waiting for a commit/rollback because the SPID is sleeping.

    The developers have not been able to find anything that would be cause this.

    Chris Cronrath

    DBA

    Delta Health Technologies

  • June 6, 2016 at 12:52 am

    #1883300

    Nothing particularly strange there. If a sleeping connection still has a transaction open, it will still hold locks. You can get an idea where the problem is by querying sys.dm_exec_connections and using the latest_sql_handle to get the last executed statement from sys.dm_exec_sql_text.

    Common causes:

    - 'nested' transactions, where there's more begin transaction statements than commit

    - improper error handling, returning to the app without running a commit/rollback.

    It's a code problem in most cases

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 7, 2016 at 7:21 am

    #1883561

    Thanks for the info I will talk to Development again.

    The weird thing about this issue is there seems to be more idle blocking when the system is less busy. This idle blocking was occurring all day Sunday and up until Monday morning around 8am. During the busiest time of the day 8am until around 4pm we see very little idle blocking. Idle blocking increased again from 4pm last night until around 7:30 this morning and now has not occurred since 7:30am but the system is much busier now.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply