TRUNCATE TABLE and ROLLBACK TRAN

  • 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

  • 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]

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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

  • 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/

  • 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/

  • 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

  • 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 58 total)

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