Lots of inserts? Part I

Question

Lots of inserts? Part I

Viewing 10 posts - 31 through 39 (of 39 total)

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

chgn01 SSCarpal Tunnel Points: 4336 More actions · Answer 1

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

Revenant SSC-Forever Points: 42467 More actions · Answer 2

It took me a bit of my lunch break but it was worth it - nice question. Thanks, Kenneth!

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 3

fun question. Thanks!

... followed it up with

COMMIT

GO 99

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

George Vobr SSCrazy Eights Points: 9966 More actions · Answer 5

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.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 6

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

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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

mkdm Ten Centuries Points: 1093 More actions · Answer 8

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!

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 9

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:-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)

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!!!

Marcia J SSCertifiable Points: 5649 More actions · Answer 10

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. 🙂