uncommitted transactions

  • I have a script that contains about 10 queries to import records from tables in one db to another. I have the whole script wrapped in Transaction and Try/Catch Block (see below). The script runs fine, but when I try to close the window, I get the message "There are uncommitted transactions. Do you wish to commit these transactions before closing the window? ". Why do I have uncommitted transactions and how do I find out what they are?

    BEGIN TRANSACTION

    BEGIN TRY

    ........code, code, code......

    ........

    .......

    END TRY

    BEGIN CATCH

    print ERROR_MESSAGE() + ' Error #' + CAST(ERROR_NUMBER() AS varchar)

    IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION

    END CATCH

    IF @@TRANCOUNT > 0

    COMMIT TRANSACTION

    GO

  • Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database.

    Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....

    Syntax is here: http://msdn2.microsoft.com/en-us/library/ms182792.aspx

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I don't see implicit transaction in the properties, how do I check it?

  • Since there are several places that it could have been turned on, why not verify that that is your problem first?

    I'd just add a SET IMPLICIT_TRANSACTIONS OFF to the code and see if the behavior goes away. If it does, then check the Connections under Server Properties, under the Tools->Options->Query Execution->SQL Server->ANSI, etc.

    If it doesn't, it might help if you posted what the code inside of the transaction is doing, as there might be some nesting going on.

  • This helped me out in a pinch. I was messing with my ANSI settings trying to learn what the ANSI nulls changed, and accidentally turned on implicit transactions. I was debugging a webservice at the time, and couldn't figure out why the DB kept locking the table! I popped the message I was getting when closing my query window and ran across this thread.

    Well I found the implicit transactions setting and turned that off. Now I am back on my merry way.

    Whew! Thanks guys 😉

  • I had the same problem this week, also had a script that called a SP a few times. After running the script and trying to close it the window 'There are uncommitted transactions. Do you wish to commit these transactions before closing the window?' popped up. I checked the SET IMPLICIT_TRANSACTIONS setting but found that it was off. I then checked the code in my SP and noticed that my BEGIN TRANSACTION statement was outside/before of my TRY block, I moved it in the TRY block and saved the SP. My script now runs without any uncommitted transaction windows popping up.

    Just thought I would share my solution. 🙂

  • Matt Miller (#4) (11/7/2007)


    Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database.

    Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....

    Syntax is here: http://msdn2.microsoft.com/en-us/library/ms182792.aspx%5B/quote%5D

    [font="Verdana"]But DB is in unstable state, as for my example i have transacted over 2 tables but accidentally the SQL connection was closed.

    How to get rid of uncommitted transaction!

    Should kill the process from sysprocess?

    Thank u?

    [/font]

  • Thanks mate for that comment. It, helped me out.

    What is annoying me is that on MSDN examples, the BEGIN TRANSACTION statement is outside the TRY..CATCH block...

  • Hi

    Yesterday ,I received the same message like

    "There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"

    and fully surprised that why it is coming .

    While googling I found so many reply to check the setting of Sql server , But I was not satisfied with all the reply then I started looking in to the code.

    I changed the format like

    BEGIN TRY

    BEGIN TRAN T1

    Insert statement

    Insert statement

    Insert statement

    Insert statement

    COMMIT TRAN T1

    END TRY

    BEGIN CATCH

    if @@ERROR <> 0

    ROLLBACK TRAN T1

    END CATCH

    And after this modification while closing the code it was not throwing any message .

Viewing 9 posts - 1 through 8 (of 8 total)

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