SQL SERVER – Rollback TRUNCATE Command in Transaction

This is very common concept that truncate can not be rolled back. I always hear conversation between developer if truncate can be rolled back or not.

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Update: (Based on comments of Paul Randal) Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

Following example demonstrates how during the transaction truncate can be rolled back.

The code to simulate above result is here.

USE tempdb
GO
-- Create Test Table
CREATE TABLE TruncateTest (ID INT)
INSERT INTO TruncateTest (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
GO
-- Check the data before truncate
SELECT * FROM TruncateTest
GO
-- Begin Transaction
BEGIN TRAN
-- Truncate Table
TRUNCATE TABLE TruncateTest
GO
-- Check the data after truncate
SELECT * FROM TruncateTest
GO
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check the data after Rollback
SELECT * FROM TruncateTest
GO
-- Clean up
DROP TABLE TruncateTest
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

67 thoughts on “SQL SERVER – Rollback TRUNCATE Command in Transaction

  1. Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations. How do you think it can be rolled-back if it isn’t logged? Rollback is *purely* driven by what’s in the transaction log.

  2. Based on your statement, “TRUNCATE is DDL operation and it is not logged in log file.” How can you rollback a truncate? It has to be logged. Reference Paul Randal posts and many others.

  3. Thank you Paul,

    I meant the same that truncate does not log removing record.

    Thanks to your comment. I have reflected your comment in my blog post.

    It is more clear now.

    Kind Regards,
    Pinal

  4. Can we rollback particular row truncated from any table??
    Mr. Pinal can you brief more on Truncate and Delete??

    Warm Regards,
    Bhavank

  5. Hi Pinal,

    As per you post, Truncated record can be rollback if we define transaction at begining and we can recover the truncated record after rollback it. But can not recover truncated records from the transaction log backup after commited truncated transaction.

    Am i right?

      • Hi Pinal,

        I have tried with follwing

        1.
        create a database
        create table
        insert records
        Backup full of the database – Fullbackup1

        2.
        backup log the same database – Trnbackup1

        3.
        Apply truncate statement with and without transaction.
        I have tried with both.

        4.
        Restore database full backup Fullbackup1
        apply trnsaction log backup Trnbackup1

        Can you tell me what happen here?

        I am able to recovred the truncated records saucessfully after applying all the above.

        Can you brief me in which scenario we can not recover truncated data?.

          • Hello Pinal,

            Following is sql code which you requested. I am ready to meet whenever you say.

            1. — create a database

            create database truncatetest
            go
            alter database truncatetest set recovery full
            use truncatetest
            go

            2. — Creating table and Inserting records
            create table tbl_truncatetest
            (id int)
            insert into tbl_truncatetest
            select 1
            union all
            select 2
            union all
            select 3
            select * from tbl_truncatetest

            3. — Taking full backup and log backup
            backup database truncatetest to disk = ‘e:\truncatetest_data.bak’
            backup log truncatetest to disk = ‘e:\truncatetest_log.trn’

            4. — Apply truncate statement on table
            begin tran
            truncate table tbl_truncatetest
            commit tran

            5. — Restoring full and log backup
            restore filelistonly from disk = ‘e:\truncatetest_data.bak’
            restore database truncatetest_1 from disk = ‘e:\truncatetest_data.bak’
            with replace ,
            move ‘truncatetest’ to ‘E:\SQL_2005_DB\truncatetest_1.mdf’,
            move ‘truncatetest_log’ to ‘E:\SQL_2005_DB\truncatetest_1_log.LDF’,
            norecovery

            restore log truncatetest_1
            from disk = ‘e:\truncatetest_log.trn’
            with recovery

            5. — Viewing the data in table
            use truncatetest_1
            go
            select * from tbl_truncatetest
            — and the data is recover

            6. — drop databases
            drop database truncatetest
            drop database truncatetest_1

      • HI Pinal
        I want to count column name
        eg.
        suppose we have a table employee with id,name,no then it will give output 3
        suppose we have student table with id,name,rollno,class then it will give 4
        how can we count only column names from table defination.
        Tejas.

  6. Nice post with examples. This clears my doubts as well. In some interview the person asked me the same with ALTER TABLE in stead of TRUNCATE in transaction, i.e.

    BEGIN TRAN
    UPDATE TESTME
    SET COL2 = 30 WHERE COL1 = 3

    ALTER TABLE TESTME
    ADD COL3 VARCHAR(100)

    ROLLBACK TRAN

    SELECT * FROM TESTME

    and he said the updates will be commited in this case. I also heard that in Oracle the updates will be committed in this case. Is it?

    Thanks,
    Prashant

    • @Paresh,

      This is quite common/usual that when you have taken backup of the database before issuing truncate and later on if you restore database then definitely you will get all the records back.

      So whats new in this. could you please highlight. as i am doing this since past four year and use to suggest my team member to take backup before any such kind of fatal command so that later on we can restore it.

      Also we have a backup server where we use to take backup regularly.

      Therefore I raised this point that it is quite common practice that once you have taken backup of database later on after restoring the same will give you all records back.

      @Pinal,
      I was not aware of that whether the truncate command being the part of transaction can be rolled-back. as I had not got any opportunity to try this in my career.

      So thanks for the tip.

  7. Paresh,

    In your case, you have taken the log backup before the truncate operation. When you restore the log with original backup it will still show you the data.

    If you take your backup of log after truncate operation, and you restore you will not able to see the data.

    In fact, the same thing will happen in case of DELETE without where condition.

    However, Truncate de-allocates the pages which stores the data and you will be not able to do point in time recovery. In case of delete if you have deleted data (I am assuming in chunks/parts) you will be able to recover the data using point in time recovery.

    • Hello Pinal,

      If i take the log backup after the truncate the table and then restore the log then obviously i can not able to see the data because it was truncated , even i take log bakcup after delete the table data and then restore it and can not able to see the data, too.
      In both of the Truncate and delete i can not able to see the data. In this case truncate and delete both are working same.
      So, In which scenario and exectly what is the difference between truncate and delete in case of the recovery?.
      Can you please give me the case when we can not recover the data for truncate and can recover the data for delete for the transaction log backup.

      Thank you.

      • Paresh,

        You can do point of time recovery if you have used delete statement. If you have used truncate you will be not able to do that. Also you will be not able to explore log using third party tools.

        Kind Regards,
        Pinal

        • What I mean by Point of Time Recovery with Delete is that you can move forward with time such that you have needed data available, whereas in case of TRUNCATE it happens all at once, so you will not have the data available.

          Do send me email and we can discuss this further if you are still confused.

  8. Hi Pinal,

    I have sent you email regarding our need of backup and strategic maintenance planning.

    It is for our client.

  9. Hi Pinal,

    Thanks for nice article. Since it is DDL and logging page allocations / deallocations.

    Can we run this in Database Mirroring without invalidating the mirroring? Most of the DDL operations can be mirrored from 2005 onwards…So it should be? Kindly explain its effect on Replication/Logshipping as well. Thanks!!!

  10. Hi Neeraj,

    In Log shipping the log backup are restored, thus the truncate command will certainly be reflected on the secondary with out any issues.
    In Replication and Mirroring transaction are applied on the secondary server, thus the truncate command will be reflected.

  11. hi all

    I want to delete the row from the sql server table.

    When i try to delete by using the following query
    delete from person where personID = 9008210

    it shows like 1 row affected

    but it is not deleting from table

    select * from table where personID = 9008210

    it displaying the data from the table

    I don’t know Why it is not deleting permanently from the table

    I try to delete manually from the table also

    but same problem occurs repetaedly

    Can any help me regarding this issue

    I appreciate it
    thank u

  12. hello pinal,

    delete can be rolled back where as truncate can not be rolled
    back in sqlserver 2005.

    Begin transaction
    truncate table sample
    rollback transaction

    Above staements are executed i got the data of sample table.

    can u explain clearly?

    regards
    kalyan

  13. hey all,

    this is the most simple and healthiest discussion i have seen ever!
    thnx for posting everyone…

    @Pinal – i do have a doubt though. I’ve seen a query from Mr. Paresh – once the transaction is committed, u will not be able to rollback. Your response too indicated that this is so.
    My doubt is – even if the statement used was “Delete” instead of “Truncate”, would there be a change in the output? i mean to say, no matter what the command is (DELETE or TRUNCATE), once a TRANSACTION is COMMITTED, WE CANNNOT RETREIVE THE RECORDS. AM I CORRECT???

  14. Mr. Pinal

    i have some clarification regading this how DDL statement invole in begin and commit tran.

    begin Tran udhaya
    create table ad123
    (
    id int,
    name varchar(10)
    )
    rollback Tran udhaya

    select * from ad123
    –Invalid object name ‘ad123′.

    begin Tran udhaya
    create table ad123
    (
    id int,
    name varchar(10)
    )
    commit Tran udhaya

    select * from ad123
    –return row set

  15. Hi Pinal,

    Acording to my knowledge,

    DELETE : DML Operation, can RollBack, logs Page Deallocation, logs Record Removal.

    TRUNCATE : DDL Operation, can RollBack, logs Page Deallocation, doesn’t logs Record Removal.

    and I hope it true.

    In case of TRUNCATE its true that it can be Rollbacked if it is executed inside Begin..RollBack Tran. And is same for DELETE also…

    If you can, please give a description about getting the deleted records from it log file. I know it might be something related to DB Administration.

    Regards,
    NandaKumar

  16. Is this something that SQL server behaves weirdly or same holds good with Oracle ? However, from my practice both DDL and DML are dependant on TRANs if used within begin tran…… rollback tran or commit tran

  17. Hi,

    using the truncate in the following way in a transaction it will roll back

    CREATE TABLE TruncateTest (ID INT)
    INSERT INTO TruncateTest (ID)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    GO
    — Check the data before truncate
    SELECT * FROM TruncateTest
    GO
    — Begin Transaction
    BEGIN TRAN
    — Truncate Table
    TRUNCATE TABLE TruncateTest

    — Check the data after truncate
    SELECT * FROM TruncateTest

    — Rollback Transaction
    ROLLBACK TRAN

    GO
    SELECT * FROM TruncateTest
    Go

  18. HI Pinal please refer me a page from your Website where I can Learn about Memory allocations ,issues,resolutions for them .
    Also A guy from Microsoft once told me that for my server whatever memory I get on my Server SQL will only use 4 GB’s of it…
    can u help me in what Kind,of scenerio this situation may arise.
    In case there is any Server Configuration or verion that uses only 4gb ram
    FYI:
    I have 16 gb ram ,windows 2003(32 bit),sql server standard edition sp2.
    would really appreciate your response on the same .Thx in advance

  19. i have to sql query for insert into two table main and detail.i want to insert into first in main table by store prosedue sp1 and 2nd in detail table by store prosedue sp2 but i want to that if there is in any error occurred at the time of insert into any table then all record inserted into both table is roll back.

    please provide code

  20. Hi Pinal,

    Is that same thing happens whe we use bulklogged ? can we rollback a BCP as bulklogged uses the same concept of logging only deallocations of data. ?

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

  22. Hi All,

    One little help plz..

    Declare @T table(A int, B char(1))
    insert into @T values(1,’A’),(15,’A’),(13,’B’),(1,’A’)

    BEGIN TRY

    BEGIN TRAN

    delete from @T where B=’A’
    insert into @T values(12,’A’)
    insert into @T values(‘2k’,’A’)
    insert into @T values(35,’A’)

    COMMIT

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    select * from @T

    ———————————————————-

    I want to ROLLBACK the whole transaction so that in any case of error or uncommitted transactions , I can have the old data at least.

  23. Dear All,
    Can you clear me about Delete Command in sql server?
    if i delete the data from the table then how can i rollback that row?
    Because as we read about delete command : we can rollback the deleted data but not in Truncate Command.
    Please clarify ….

    Thanks

  24. Hi Pinal,

    Your blogs are always helpful and want to say thanks for that. I have got a problem in replication
    I want to use truncate table statement in a replication environment.But unable to use it as it’s giving error. Currently the replication is a transitional replication only.
    My database is SQL SERVER 2012 Standard Edition.
    I can not go for snapshot replication due to some restrictions.
    Can you please help me with this problem.

  25. Hi Pinal,

    I am Using SQL Server 2005
    Suppose we have Table Tutorials_tbl
    Create Table Tutorials_tbl (tutorial_ID INT)
    We have inserted the 3 values
    INSERT INTO dbo.Tutorials_tbl
    Select ‘1’, Union All
    Select ‘2’,Union All
    Select ‘3’,

    Select * from Tutorials_tbl
    tutorial_ID
    1
    2
    3

    Delete From Tutorials_tbl (DELETE command will delete all the data in the table)
    Truncate Table Tutorials_tbl (TRANCATE command will delete all the data in the table)

    If we had use the TRANCATE or DELETE either of the command will have same effect in the Table Tutorials_tbl (Command will delete all the data in the table)
    Select * from Tutorials_tbl
    tutorial_ID tutorial_title tutorial_author submission_date

    If we had use the TRANCATE or DELETE either of the command by using BEGIN TRANSACTION (Like below)
    For TRANCATE
    Begin Tran
    Truncate Table Tutorials_tbl
    Go
    For DELETE
    Begin Tran
    Delete From Tutorials_tbl
    Go

    We had Recovered the Data by Below command using for both TRANCATE OR for DELETE
    Rollback tran

    Then what is the difference between them to recover the data? Why we say that Delete can be rolled back if we can’t rollback if we are not using Transaction? Please tell me command to get the data back. Please clear my doubts…

    Would be waiting for your Reply.

    Thanks
    Brijesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s