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)
69 Comments. Leave new
Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations. How do you think it can be rolled-back if it isn’t logged? Rollback is *purely* driven by what’s in the transaction log.
Based on your statement, “TRUNCATE is DDL operation and it is not logged in log file.” How can you rollback a truncate? It has to be logged. Reference Paul Randal posts and many others.
Thanks Pinal,
I did not know this. Very simple and easy example.
Can we rollback particular row truncated from any table??
Mr. Pinal can you brief more on Truncate and Delete??
Warm Regards,
Bhavank
Hi Pinal,
As per you post, Truncated record can be rollback if we define transaction at begining and we can recover the truncated record after rollback it. But can not recover truncated records from the transaction log backup after commited truncated transaction.
Am i right?
Nice post with examples. This clears my doubts as well. In some interview the person asked me the same with ALTER TABLE in stead of TRUNCATE in transaction, i.e.
BEGIN TRAN
UPDATE TESTME
SET COL2 = 30 WHERE COL1 = 3
ALTER TABLE TESTME
ADD COL3 VARCHAR(100)
ROLLBACK TRAN
SELECT * FROM TESTME
and he said the updates will be commited in this case. I also heard that in Oracle the updates will be committed in this case. Is it?
Thanks,
Prashant
No. Updates will not be commited
Everything will be rollback no matter whether it is update,delete or ddl statements
For Oracle, :
Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.
So, it cannot be ROLLed BACK
So, only option to undo is to again execute a DDL statement?
Then I dont see any point in allowing them inside a transaction
Thanks Brian, this clears my concept…
@Paresh,
This is quite common/usual that when you have taken backup of the database before issuing truncate and later on if you restore database then definitely you will get all the records back.
So whats new in this. could you please highlight. as i am doing this since past four year and use to suggest my team member to take backup before any such kind of fatal command so that later on we can restore it.
Also we have a backup server where we use to take backup regularly.
Therefore I raised this point that it is quite common practice that once you have taken backup of database later on after restoring the same will give you all records back.
@Pinal,
I was not aware of that whether the truncate command being the part of transaction can be rolled-back. as I had not got any opportunity to try this in my career.
So thanks for the tip.
thanks for telling us, no one is as clear as you. You are good with images.
Hi Pinal,
I have sent you email regarding our need of backup and strategic maintenance planning.
It is for our client.
Excellent, You explained it very nice. I got it right away.
This is the reason, why we come to visit your blog.
This is Joe Again,
my office mate says you should change your domain name to SimpleSQL.com or SolidSQL.com
The current site already got popular
There is no point in changing it
Hi Pinal Sir,
Its Marvelllous ,Nodoubt You are the best SQL Player or challenger.
Yes you are rit sarika
Nice article, a very healthy discussion.
Thank you
Hi Pinal,
Thanks for nice article. Since it is DDL and logging page allocations / deallocations.
Can we run this in Database Mirroring without invalidating the mirroring? Most of the DDL operations can be mirrored from 2005 onwards…So it should be? Kindly explain its effect on Replication/Logshipping as well. Thanks!!!
Hi Neeraj,
In Log shipping the log backup are restored, thus the truncate command will certainly be reflected on the secondary with out any issues.
In Replication and Mirroring transaction are applied on the secondary server, thus the truncate command will be reflected.
hi all
I want to delete the row from the sql server table.
When i try to delete by using the following query
delete from person where personID = 9008210
it shows like 1 row affected
but it is not deleting from table
select * from table where personID = 9008210
it displaying the data from the table
I don’t know Why it is not deleting permanently from the table
I try to delete manually from the table also
but same problem occurs repetaedly
Can any help me regarding this issue
I appreciate it
thank u
Hello Usha,
Are you using SET IMPLICIT_TRANSACTIONS ON in your session?
Regards,
Pinal Dave
hello pinal,
delete can be rolled back where as truncate can not be rolled
back in sqlserver 2005.
Begin transaction
truncate table sample
rollback transaction
Above staements are executed i got the data of sample table.
can u explain clearly?
regards
kalyan
Can we rollback particular row truncated from any table??
Regards
Azad
By rollbaking truncate, the table gets all data in whcih you can find the required row
Actually TRUNCATE belongs to which command?
It is DDL (Data Definition Language)
SQL Interview Questions with Answers
states:
…
What is difference between DELETE & TRUNCATE commands?
…
TRUNCATE
…
TRUNCATE can not be Rolled back.
…
Provided it is not used inside a transaction