Lots of inserts? Part I

  • chgn01

    Hall of Fame

    Points: 3537

    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

    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

    fun question. Thanks!

    ... followed it up with

    COMMIT

    GO 99

  • SQLRNNR

    SSC Guru

    Points: 281210

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

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

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • TomThomson

    SSC Guru

    Points: 104772

    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

    SSC Eights!

    Points: 894

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

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

    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 40 (of 40 total)

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