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. 

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.

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)