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 TableCREATE TABLE TruncateTest (ID INT)INSERT INTO TruncateTest (ID)SELECT 1UNION ALLSELECT 2UNION ALLSELECT 3
GO-- Check the data before truncateSELECT * FROM TruncateTest
GO-- Begin TransactionBEGIN TRAN-- Truncate TableTRUNCATE TABLE TruncateTest
GO-- Check the data after truncateSELECT * FROM TruncateTest
GO-- Rollback TransactionROLLBACK TRANGO-- Check the data after RollbackSELECT * FROM TruncateTest
GO-- Clean upDROP TABLE TruncateTest
GO