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.

SQL SERVER - Rollback TRUNCATE Command in Transaction truncaterollback

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 (https://blog.sqlauthority.com)

Solarwinds
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