SQL SERVER – Rollback TRUNCATE Command in Transaction

This is a very common concept that truncate cannot be rolled back. Let us learn in today’s blog post that Rollback TRUNCATE is possible. I always hear the conversation between the developer if truncate can be rolled back or not. Let me just say it clearly today – TRUNCATE can be rolled back. 

SQL SERVER - Rollback TRUNCATE Command in Transaction rollback-truncate-800x418

Truncate is indeed a logged operation, it just does not log removing the records but it logs the page deallocations. Whereas DELETE command actually logs every single record it removes from the table, hence it takes a bit longer to execute.

In any case, both of the keywords are logged and can be rolled back.

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

SQL SERVER - Rollback TRUNCATE Command in Transaction truncaterollback

The code to simulate the above result is here.

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

Well, I hope you find this example fun and also removes any confusion you may have on this topic. Here are a few related blog posts you should read on the same topic.

SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed

I hope from today’s blog post it is clear that rollback truncate command is possible. You can watch my videos on my youtube channel over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQLAuthority News – Excellent Event – TechEd Sri Lanka – Feb 8, 2010
Next Post
SQL SERVER – Order of Columns in Update Statement Does not Matter

Related Posts

69 Comments. Leave new

  • Actually TRUNCATE belongs to which command?

    Reply
  • ricky lively
    May 4, 2010 1:36 am

    SQL Interview Questions with Answers
    states:


    What is difference between DELETE & TRUNCATE commands?

    TRUNCATE

    TRUNCATE can not be Rolled back.

    Reply
  • Really, your approach is so simple to describe any topics.

    Reply
  • 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???

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

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

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

    Reply
  • Your every concepts are beneficial to me and i read carefully every day .I specially thanks you.

    Thanks in advance
    dilip prakash

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

    Reply
  • Muhammad Imran
    April 9, 2012 4:34 pm

    Hi Pinal,
    I recently developed a script that can recover the truncated data from the log/ data page.

    Imran

    Reply
  • Jainender Sharma
    April 15, 2012 12:33 pm

    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

    Reply
  • Hi, Kunal

    In sqlserver all three action Delete,Truncate and Drop is passible.

    Am I right ?

    Reply
  • Hi, Kunal

    In sqlserver Roll Back in all three action Delete,Truncate and Drop is passible.

    Am I right ?

    Reply
    • Jainender Sharma
      May 16, 2012 4:00 pm

      I am Hi Asif this is jai
      The Delete and truncate can be rolled back for sure but I would really Like to know if DROP can also be rolled back in case you use it in a transaction clause

      Reply
  • Akhilesh pandit
    June 14, 2012 11:38 pm

    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

    Reply
    • You need to use a wrapper procedure that has transaction and call other procedures. This way if there is any error, you can rollback

      Reply
  • Can we perform truncate on Create command as well ?

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

    Reply
  • if has deleted datas in our server no backup then how to take that datas

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

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

    Reply
  • padmalochan1986
    March 28, 2014 11:35 pm

    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.

    Reply

Leave a Reply

Menu