July 27, 2016 at 12:22 pm
Thank you George Vobr, you are right, but if you do not use tempdb, it will not give you the information. The @@trancount will do.
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
July 27, 2016 at 12:34 pm
It took me a bit of my lunch break but it was worth it - nice question. Thanks, Kenneth!
July 27, 2016 at 2:54 pm
fun question. Thanks!
... followed it up with
COMMIT
GO 99
July 27, 2016 at 3:57 pm
Nice enjoyable question today.
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2016 at 5:11 pm
chgn01 (7/27/2016)
Thank you George Vobr, you are right, but if you do not use tempdb, it will not give you the information. The @@trancount will do.
Thanks for Your post. I originally tried the script on a normal table with USE TestDB.
The results are the same as when using the tempdb. DBCC OPENTRAN information
about active transactions always be displayed. Maybe it depends on the used code
or the version of SQL Server.
I remain with best regards G.V.
July 28, 2016 at 12:41 am
Really nice question!
Missed that we did not have a batch terminator after "BEGIN TRAN" and that we were dealing with nested transactions here.
Enjoyed the question thoroughly. Thank-you very much!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
July 28, 2016 at 7:46 am
manie (7/27/2016)
I tested the COMMIT by adding COMMIT TRAN and still had 99 open transactions so I take it the GO 100 must be it and I also have never had any use for that. How would a person then make sure that each row/transaction has been committed? This is the reason why I like QotD so much because I learn things that I have never done or thought of doing before.:-D:-D:-D:-D:-D
In cases where it's not be clear how many commits are needed, you need to do one of two things: (i) fire the developer or dba responsible or (ii) arrange remedial training for the developer/dba responsible.
Of course you also have the option of using
WHILE @@TRANCOUNT>0 COMMIT
instead of a single commit, but I don't reccomend that as a way of avoiding getting the code fixed so that it's always absolutely clear what the pseudo-transaction nesting level is. ("pseudo-transaction" because real nested transactions aren't available in T-SQL since rollback always rolls back the whole nest, not just the current transaction)
Tom
July 28, 2016 at 11:36 am
Great QOTD and discussion, especially on batch boundaries and ROLLBACK of nested transactions. I was unsure whether BEGIN TRAN was part of the batch, as well as the effect of that final COMMIT. This is one of those questions that it was better to get wrong. 🙂
Thanks, Kenneth!
July 29, 2016 at 12:28 am
TomThomson (7/28/2016)
manie (7/27/2016)
I tested the COMMIT by adding COMMIT TRAN and still had 99 open transactions so I take it the GO 100 must be it and I also have never had any use for that. How would a person then make sure that each row/transaction has been committed? This is the reason why I like QotD so much because I learn things that I have never done or thought of doing before.:-D:-D:-D:-D:-DIn cases where it's not be clear how many commits are needed, you need to do one of two things: (i) fire the developer or dba responsible or (ii) arrange remedial training for the developer/dba responsible.
Of course you also have the option of using
WHILE @@TRANCOUNT>0 COMMIT
instead of a single commit, but I don't reccomend that as a way of avoiding getting the code fixed so that it's always absolutely clear what the pseudo-transaction nesting level is. ("pseudo-transaction" because real nested transactions aren't available in T-SQL since rollback always rolls back the whole nest, not just the current transaction)
Thanks Tom, I think I know what to do now.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
August 24, 2016 at 2:51 pm
manie (7/27/2016)
... This is the reason why I like QotD so much because I learn things that I have never done or thought of doing before.:-D:-D:-D:-D:-D
Completely agree. 🙂
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply