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

  • Brijesh Mehra
    May 11, 2014 1:53 pm

    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

    Reply
  • .NET (.Net Expert Team)
    March 17, 2015 12:46 pm

    Hello dear, My question is how can i get my deleted or updated data from table in sql server using rollback functionality.

    Reply
  • Nihar Kulkarni
    May 30, 2015 10:31 pm

    Hi ,
    I have question related to Rollback Transaction

    My test script as
    ——————————————————————
    –CREATE TABLE ABC
    –(
    — ID BIGINT IDENTITY(1,1),
    — VID BIGINT
    –)

    BEGIN TRANSACTION

    INSERT INTO ABC(VID) VALUES (2)

    SELECT * FROM ABC

    ROLLBACK TRUNCATE
    ————————————————————

    Now my identity column value doesn’t reset.

    While we test T-SQL Script on production , we have to maintain identity column.

    How to avoid such corrections or identity creation from rollback ?

    Reply

Leave a Reply

Menu