Post reply

Table locked after t-sql runs

  • June 12, 2008 at 5:34 am

    #183426

    Really weird issue. Someone may have come across this before...

    I run a batch of sql code. I actually see that it gets to the final step and then finishes. But it leaves a lock on the table that I have to kill. The job is finished, but is showing as sleeping on an sp_who2.

    Anyone seen this behaviour before?

  • June 12, 2008 at 8:00 am

    #827870

    There are very few reasons why this may happen...

    --Uncommitted transaction

    Do a transaction count at the end of the proc to find out. Might take a bit to find if your code is heavy RBAR with lot's of calls.

    --Improperly closed object

    Did you leave a scrollable cursor open? Did you use an sp_OA* sproc and forget to close an object?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2008 at 8:52 am

    #827919

    COMMIT had been commented out! :crazy:

    Thanks

  • June 12, 2008 at 2:19 pm

    #828111

    That's a common mistake that many developers make... they comment something like that out so they can test and do a rollback... but they forget to put things back the way the were. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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