Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

uncommitted transactions Expand / Collapse
Author
Message
Posted Wednesday, November 07, 2007 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 04, 2009 1:49 PM
Points: 100, Visits: 100
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
Post #419626
Posted Wednesday, November 07, 2007 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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?
Post #419639
Posted Thursday, November 08, 2007 1:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 04, 2009 1:49 PM
Points: 100, Visits: 100
I don't see implicit transaction in the properties, how do I check it?
Post #420249
Posted Thursday, November 08, 2007 1:46 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 08, 2008 8:42 AM
Points: 775, Visits: 214
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.
Post #420268
Posted Tuesday, April 22, 2008 2:44 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:36 PM
Points: 7, Visits: 59
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 ;)
Post #488934
Posted Thursday, September 04, 2008 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 06, 2011 6:44 AM
Points: 206, Visits: 363
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. :)
Post #563762
Posted Thursday, July 01, 2010 12:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:51 AM
Points: 209, Visits: 1,085
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



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?
Post #945952
Posted Tuesday, December 07, 2010 1:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 1:40 AM
Points: 2, Visits: 2
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...
Post #1031080
Posted Tuesday, January 31, 2012 2:47 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 685, Visits: 146
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 .
Post #1244154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse