Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


uncommitted transactions


uncommitted transactions

Author
Message
Stef Teal
Stef Teal
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7651 Visits: 18084
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?
Stef Teal
Stef Teal
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 100
I don't see implicit transaction in the properties, how do I check it?
David McFarland
David McFarland
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 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.
orig.beetlebub
orig.beetlebub
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 Wink
Mark Beringer-730383
Mark Beringer-730383
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
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. Smile
Abrar Ahmad_
Abrar Ahmad_
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 1288
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?

Wodzu
Wodzu
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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...
amit_adarsh
amit_adarsh
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 169
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 .
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search