Post reply

Open Transaction!!

  • January 20, 2012 at 4:11 pm

    #1436764

    problem has come back, that 'UPDATE' is still running in the background blocking all the commands I run !

  • January 21, 2012 at 3:32 am

    #1436794

    On my server, session 30 is a service broker task.

    I'm no service broker expert, but I suspect the problem with service broker (whatever it is) is causing this issue, not the other way around. I suggest you start by investigating why service broker is not working.

    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

  • January 26, 2012 at 11:48 am

    #1438967

    Gail

    It turned out to be quite interesting in the end.

    In my service broker queue activation proc there was a transaction

    followed by bunch of if else statements

    in one of the IF block the transaction was getting stuck.

    I explicitly wrote 'commit' in the block and ran the proc

    and the transaction got committed once in for all

    then I reverted back and removed the commit statement.

    now the problem is permanently resolved.

Viewing 3 posts - 16 through 17 (of 17 total)

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