TRUNCATE TABLE and ROLLBACK TRAN

  • Revenant

    SSC-Forever

    Points: 42467

    David Burrows (2/8/2012)


    GilaMonster (2/6/2012)


    Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

    Or maybe create MotD (Myth of the Day) 😀

    They seem to pop up on their own, without anyone really trying.

  • TomThomson

    SSC Guru

    Points: 104773

    GilaMonster (2/6/2012)


    Is it bad form, if you create a QotD, to list your own article as a reference?

    depends who does it. It won't be bad form if you do it.

    Tom

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good question. I missed it but learned something.

    http://brittcluff.blogspot.com/

  • Kwex

    Default port

    Points: 1407

    Good question ... thanks!

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    Nice to test and show answer as absolute.

    Jamie

  • BudaCli

    Hall of Fame

    Points: 3383

    Nice & e@zy

    What you don't know won't hurt you but what you know will make you plan to know better
  • kapil_kk

    SSC-Insane

    Points: 21316

    Ahhh... 🙁

    I thought Recovery mode has effect while rolling back the transaction.

    well new thing to learn today...

    Can any give an example of this?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Recovery model has no effect ever on rolling back a transaction.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hugo Kornelis

    SSC Guru

    Points: 64685

    kapil190588 (10/11/2012)


    Can any give an example of this?

    Yesterday, when you asked for resources to learrn about SQL Server, one of the suggestions I gave (the first, to be precise) is to make sure you have an instance of SQL Server that you can use to play around with. Now is the time to use that instance.

    It is really very easy to create this example yourself. Just create a table, put in a few rows of data, then start a transaction, truncate the table, rollback the transaction and then check the contents of the table.

    That example is so basic, that just typing and posting the code would probably have taken me less time than it took me to write this message. But I hope this message has the higher educational value.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Since I worked only in 2005 & above i think got it correct. I don't have idea about earlier versions.

    As far as I know anything written between

    BEGIN TRAN

    .

    .

    .

    ROLLBACK

    can be rolled back.

    Please correct me if i'm wrong.

    --
    Dineshbabu
    Desire to learn new things..

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    Inserts into a table variable aren't rolled back. "SELECT NEXT VALUE FOR <sequence>" isn't rolled back either. I'm not aware of anything else that isn't but there's likely a small number that aren't.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Not just inserts into a table variable, all modifications to table variables are retained after a rollback.

    Other things that are not rolled back are assignments to scalar variables, identity values that were givven out are not given out again (so you can get gaps in the identity sequence), and you can still observe the effects of a rolled back transaction in most dynamic management views.

    On a more technical side, modifications written to the transaction log are not removed on a rollback (there is in fact an extra entry added, to log the rollback event). And I think (but have not tested) that an autogrow of the database file that takes place because of a huge insert that is rolled back will not be undone.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Page splits don't roll back.

    Also, while table variables ignore user transaction rollbacks, statements will still automatically roll back in the case of an error

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dineshbabu

    Hall of Fame

    Points: 3220

    Thanks cfradenburg, Hugo & GilaMonster.

    I will take the TSQL part, Changes made to Table variables and Scalar variables will not be affected by Rollback. As well as Identity value will not be re-seeded.

    --
    Dineshbabu
    Desire to learn new things..

Viewing 14 posts - 46 through 59 (of 59 total)

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