Rollback TRUNCATE – Script – SQL in Sixty Seconds #105

One of the most popular questions, I keep on receiving is that – Is it possible to Rollback Truncate operation? The answer is YES, let me explain the entire process with example and video.

Let me confess first that when I started my career with SQL Server, I even believed that truncate operation can’t be rolled back. The reason behind this belief was that I had always heard this from others. The matter of fact, I have even blogged like that before which I eventually corrected. However, one day, I decided to try out the entire process and to my surprised that I found that it is possible to rollback truncate in SQL Server.

Today, I am going to tell the story in the video how one can rollback truncate operation.

Here are the two most popular argument, I receive on this topic.

Argument #1: Truncate can be rolled back as it is in the transactions. If you commit the transaction you can’t rollback it.

My response: Why would you try to rollback committed transactions? Let us talk about another operator like DELETE, can we rollback delete operation after committing it? I believe TRUNCATE’s behaviour is the same as DELETE, INSERT or UPDATE and it can be rolled back when used in the transactions.

Argument #2: Truncate is not logged and can’t be recovered from the log file.

My response: 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.

Argument #3: Delete is better than truncate.

My response: Truncate and Delete should not be compared at all. Truncate resets the entire table to initial state including identity column and delete just deletes the data qualifying in the WHERE condition. If you do not specify the WHERE condition, delete will remove all the rows from the table but it will not reset the identity column. They both are very different and they have different usage.

If you are looking for the script used in the video, you can download the script from my blog post written 10 years ago: SQL SERVER – Rollback TRUNCATE Command in Transaction. I am glad that I build this video so I can share it with my clients  Comprehensive Database Performance Health Check.

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