TRUNCATE TABLE and ROLLBACK TRAN

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Comments posted to this topic are about the item TRUNCATE TABLE and ROLLBACK TRAN


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - good to know my memory is not failing me

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Paul White

    SSC Guru

    Points: 150442

    Good question; there are a number of links you could have included in the explanation, but one of my favourites is this by Kalen Delaney:

    http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks to Paul Randals DBA Myth A Day. 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Thanks for the question, but please explain why:

    No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode

    isn't also correct?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • vk-kirov

    SSCertifiable

    Points: 7686

    Explanation


    there is no documentation on a non-existant concern

    I disagree. Here is the link: http://msdn.microsoft.com/en-us/library/ms191544.aspx

    Transact-SQL Statements Allowed in Transactions

    You can use all Transact-SQL statements in an explicit transaction, except for the following statements:

    ALTER DATABASE

    ALTER FULLTEXT CATALOG

    ALTER FULLTEXT INDEX

    BACKUP

    CREATE DATABASE

    CREATE FULLTEXT CATALOG

    CREATE FULLTEXT INDEX

    DROP DATABASE

    DROP FULLTEXT CATALOG

    DROP FULLTEXT INDEX

    RECONFIGURE

    RESTORE

    ...

    UPDATE STATISTICS can be used inside an explicit transaction. However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

    Based on this article, one can conclude that a TRUNCATE TABLE statement can be rolled back.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Henrico Bekker (2/5/2012)


    Thanks for the question, but please explain why:

    No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode

    isn't also correct?

    Because you can rollback a transaction with a TRUNCATE TABLE statement?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Koen Verbeeck (2/5/2012)


    Henrico Bekker (2/5/2012)


    Thanks for the question, but please explain why:

    No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode

    isn't also correct?

    Because you can rollback a transaction with a TRUNCATE TABLE statement?

    Tsk, thanks Koen....

    Slow Monday morning for me....ignore me for the day ...

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Henrico Bekker (2/6/2012)


    Koen Verbeeck (2/5/2012)


    Henrico Bekker (2/5/2012)


    Thanks for the question, but please explain why:

    No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode

    isn't also correct?

    Because you can rollback a transaction with a TRUNCATE TABLE statement?

    Tsk, thanks Koen....

    Slow Monday morning for me....ignore me for the day ...

    Okidoki 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    I wanted to comment that this question is superfluous, that everyone knows this. But.....

    At the time of writing:

    "Incorrect answers: 56% (52)"

    And 34% of them are for "you can never rollback a truncate table statement"

    .

    .

    .

    .

    .

    .

    Picks up jaw from floor.


    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/

  • vk-kirov

    SSCertifiable

    Points: 7686

    Hugo Kornelis (2/6/2012)


    And 34% of them are for "you can never rollback a truncate table statement"

    Maybe they are Oracle refugees? :hehe: http://www.sqlservercentral.com/articles/Raw+Materials/71108/

  • Brigadur

    SSCrazy

    Points: 2097

    Nice and important question. This issue is coming up time to time when discussing differences of delete and truncate operation.

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Stewart "Arturius" Campbell (2/6/2012)


    vk-kirov (2/6/2012)


    Hugo Kornelis (2/6/2012)


    And 34% of them are for "you can never rollback a truncate table statement"

    Maybe they are Oracle refugees? :hehe: http://www.sqlservercentral.com/articles/Raw+Materials/71108/

    A TRUNCATE can be rolled back in Oracle as well....

    I've never used Oracle, so I can't speak from experience - but the official Oracle documentation disagrees with you:

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm

    "Caution:

    You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated."


    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/

Viewing 15 posts - 1 through 15 (of 59 total)

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