SQL Server Performance

How to rollback Truncate operation?

Discussion in 'T-SQL Performance Tuning for Developers' started by sonnysingh, Jul 21, 2006.

  1. sonnysingh Member

    Hi All

    I have been ask few time this question that how do rollback truncation action which fired with out BEGIN TRAN and COMMIT command.. just fire the commnd (truncate table_name)... I have done some investigation regarding this issue but could not get satisfactory results.

    please help...
    Thanks in advance
  2. merrillaldrich New Member

    I don't think it's possible, except if you restore from a backup.
  3. satya Moderator

    TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  4. FrankKalis Moderator

  5. Madhivanan Moderator

    It is always better to test all Deletes and Truncates in TEST server before applying them in PRODUCTION Server

    Madhivanan

    Failing to plan is Planning to fail
  6. sonnysingh Member

    Hi Guys

    I am very glad that answers are given by all senior members... Thanks. But as Satya's explaintion that data pages are deallocated but data is still exists and extents are marked as empty for reuse. If data is still exists then is there any method we can get back (show) them on same data pages the ones we truncated or marked those extents as full (or not empty) with same data pages.

    I am try to think that some way to rollback truncate operation cos it is mentioned in BOL that Truncate command can rollback. In this way we can avoid restoration of trucated data from backup.
    Please advice...

    regards
  7. FrankKalis Moderator

  8. sonnysingh Member

    Thanks Frank... I have read the article and it is good enough to understand the difference between TRUNCATE and DELETE specially last paragraph.

    quoted from your article:
    "You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state."

    so could you please explain through practical example????? cos still confuse about how would I rollback the truncate operation from transaction log.

    thanks again
    regards

  9. FrankKalis Moderator

    Just like any other transaction


    USE tempdb
    CREATE TABLE t (c1 INT)
    INSERT INTO t SELECT BINARY_CHECKSUM(OrderID) FROM Northwind..Orders
    SELECT *
    FROM t
    BEGIN TRAN
    TRUNCATE TABLE t
    COMMIT TRAN
    SELECT *
    FROM t
    INSERT INTO t SELECT BINARY_CHECKSUM(OrderID) FROM Northwind..Orders
    BEGIN TRAN
    TRUNCATE TABLE t
    ROLLBACK TRAN
    SELECT *
    FROM t
    DROP TABLE t


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  10. sonnysingh Member

    Thanks Frank...

    But my question was if we fired TRUNCATE command without BEGIN TRAN it will logged this operation in transaction log as deallocation of data pages(as mentioned in you article).
    Then can we rollback the TRUNCATE operation in this situation. if yes then how? if not then wny not?

    thanks again....
    regards
  11. FrankKalis Moderator

    Ah, I see now. <br /><br />Well, I'm guessing now. I don't know that for sure...<br /><br />TRUNCATE marks some storage space as available for reuse. If you notice quickly after the TRUNCATE that it has been fired, and try to rollback the operation with a third-party tool, the space might have not been occupied by other data yet. The longer you wait, the more likely it is, that the physical data has been overwritten. <br /><br />Hope someone else can either verify or rectify my guess. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  12. SQLDBcontrol New Member

    quote:Originally posted by sonnysingh

    Thanks Frank...

    But my question was if we fired TRUNCATE command without BEGIN TRAN it will logged this operation in transaction log as deallocation of data pages(as mentioned in you article).
    Then can we rollback the TRUNCATE operation in this situation. if yes then how? if not then wny not?

    thanks again....
    regards


    It's not possible.

    If you run any of the below statements without a begin tran then SQL Server will automatically run an implicit transaction (unless you had IMPLICIT_TRANSACTIONS set to OFF).

    In other words, executing "truncate table mytable" is equivalent to :


    begin tran
    truncate table mytable
    commit tran


    In other words, the truncate table statement was committed and cannot be rolled back.

    Now, whether there is a third-party tool that lets you do this I honestly don't know but I'd question the wisdom of using any such tool (if it existed) since the point of having all of this transaction processing in place is to maintain consistency and you might end up with an inconsistent set of data. It would be easier and cheaper to restore the database - or restore it to another location from which you can access this one table and then insert that table's data into the original table.

    Here's the list of statements to which IMPLICIT_TRANSACTIONS applies:



    ALTER TABLE FETCH REVOKE
    CREATE GRANT SELECT
    DELETE INSERT TRUNCATE TABLE
    DROP OPEN UPDATE


    Karl Grambow

    www.sqldbcontrol.com
  13. Adriaan New Member

    Call me stupid, but if it must be reversible through a ROLLBACK TRANSACTION, then why are we not suggesting ...

    BEGIN TRAN
    DELETE FROM mytable

    IF ...
    BEGIN
    ROLLBACK TRAN
    END
    ELSE
    BEGIN
    COMMIT TRAN
    DBCC whatever it is to reset the identity column
    END
  14. SQLDBcontrol New Member

    Hi Adriaan, that would be the logical thing to do in the first place.

    But, if I understood the question correctly, the original poster wanted to know if it was possible to rollback a truncate table transaction that had already been committed, due to the fact that an implicit transaction was run by leaving out the explicit BEGIN TRAN.


    quote:
    ...how do rollback truncation action which fired with out BEGIN TRAN and COMMIT command?

    Of course, I might be wrong and have completely misunderstood the original question[:I]

    Karl Grambow

    www.sqldbcontrol.com
  15. TheSQLGuru New Member

    Adriaan, there are times when it is desireable to use TRUNCATE instead of the DELETE script you posted. The primary ones are log space and duration of execution. DELETE is a fully-logged operation, and if you have a 2GB table you may not want to have that operation logged due to log space and the time it would take to complete. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I do believe third party tools can recover the data from a TRUNCATE, but you are at the mercy of the pages having been reassigned. Personally I would restore the database to a different name and BULK insert the data from that copy. If you need modifications to the data from the point of the last restore to the time of the TRUNCATE action, use a log recovery tool to REDO the DML statements against that table from the production database's online xaction log and any saved xaction log backups since the full backup used to recover the table.<br /><br /><br />SQLGuru

Share This Page