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 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 (http://blog.SQLAuthority.com)
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.
Thank you Paul,
I meant the same that truncate does not log removing record.
Thanks to your comment. I have reflected your comment in my blog post.
It is more clear now.
Kind Regards,
Pinal
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?
Very correct Paresh.
Hi Pinal,
I have tried with follwing
1.
create a database
create table
insert records
Backup full of the database – Fullbackup1
2.
backup log the same database – Trnbackup1
3.
Apply truncate statement with and without transaction.
I have tried with both.
4.
Restore database full backup Fullbackup1
apply trnsaction log backup Trnbackup1
Can you tell me what happen here?
I am able to recovred the truncated records saucessfully after applying all the above.
Can you brief me in which scenario we can not recover truncated data?.
Can you send me your original code in email please at pinal ‘at’ sqlauthority.com ?
Additionally, we stay in same city, let us meet up some time.
Hello Pinal,
Following is sql code which you requested. I am ready to meet whenever you say.
1. — create a database
create database truncatetest
go
alter database truncatetest set recovery full
use truncatetest
go
2. — Creating table and Inserting records
create table tbl_truncatetest
(id int)
insert into tbl_truncatetest
select 1
union all
select 2
union all
select 3
select * from tbl_truncatetest
3. — Taking full backup and log backup
backup database truncatetest to disk = ‘e:\truncatetest_data.bak’
backup log truncatetest to disk = ‘e:\truncatetest_log.trn’
4. — Apply truncate statement on table
begin tran
truncate table tbl_truncatetest
commit tran
5. — Restoring full and log backup
restore filelistonly from disk = ‘e:\truncatetest_data.bak’
restore database truncatetest_1 from disk = ‘e:\truncatetest_data.bak’
with replace ,
move ‘truncatetest’ to ‘E:\SQL_2005_DB\truncatetest_1.mdf’,
move ‘truncatetest_log’ to ‘E:\SQL_2005_DB\truncatetest_1_log.LDF’,
norecovery
restore log truncatetest_1
from disk = ‘e:\truncatetest_log.trn’
with recovery
5. — Viewing the data in table
use truncatetest_1
go
select * from tbl_truncatetest
– and the data is recover
6. — drop databases
drop database truncatetest
drop database truncatetest_1
HI Pinal
I want to count column name
eg.
suppose we have a table employee with id,name,no then it will give output 3
suppose we have student table with id,name,rollno,class then it will give 4
how can we count only column names from table defination.
Tejas.
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, http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_4010.htm#i2060233:
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.
Paresh,
In your case, you have taken the log backup before the truncate operation. When you restore the log with original backup it will still show you the data.
If you take your backup of log after truncate operation, and you restore you will not able to see the data.
In fact, the same thing will happen in case of DELETE without where condition.
However, Truncate de-allocates the pages which stores the data and you will be not able to do point in time recovery. In case of delete if you have deleted data (I am assuming in chunks/parts) you will be able to recover the data using point in time recovery.
Hello Pinal,
If i take the log backup after the truncate the table and then restore the log then obviously i can not able to see the data because it was truncated , even i take log bakcup after delete the table data and then restore it and can not able to see the data, too.
In both of the Truncate and delete i can not able to see the data. In this case truncate and delete both are working same.
So, In which scenario and exectly what is the difference between truncate and delete in case of the recovery?.
Can you please give me the case when we can not recover the data for truncate and can recover the data for delete for the transaction log backup.
Thank you.
Paresh,
You can do point of time recovery if you have used delete statement. If you have used truncate you will be not able to do that. Also you will be not able to explore log using third party tools.
Kind Regards,
Pinal
What I mean by Point of Time Recovery with Delete is that you can move forward with time such that you have needed data available, whereas in case of TRUNCATE it happens all at once, so you will not have the data available.
Do send me email and we can discuss this further if you are still confused.
Hi Pinal,
I have sent you email regarding our need of backup and strategic maintenance planning.
It is for our client.
Roshni,
I have not received your email yet. I tried to send email to your address but it does not go through.
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
Hi Kalyan,
That is exactly I have explained in this blog post.
Kind Regards,
Pinal
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
http://www.sqlauthority.com states:
…
What is difference between DELETE & TRUNCATE commands?
…
TRUNCATE
…
TRUNCATE can not be Rolled back.
…
Provided it is not used inside a transaction
Really, your approach is so simple to describe any topics.
hey all,
this is the most simple and healthiest discussion i have seen ever!
thnx for posting everyone…
@Pinal – i do have a doubt though. I’ve seen a query from Mr. Paresh – once the transaction is committed, u will not be able to rollback. Your response too indicated that this is so.
My doubt is – even if the statement used was “Delete” instead of “Truncate”, would there be a change in the output? i mean to say, no matter what the command is (DELETE or TRUNCATE), once a TRANSACTION is COMMITTED, WE CANNNOT RETREIVE THE RECORDS. AM I CORRECT???
Mr. Pinal
i have some clarification regading this how DDL statement invole in begin and commit tran.
begin Tran udhaya
create table ad123
(
id int,
name varchar(10)
)
rollback Tran udhaya
select * from ad123
–Invalid object name ‘ad123′.
begin Tran udhaya
create table ad123
(
id int,
name varchar(10)
)
commit Tran udhaya
select * from ad123
–return row set
Hi Pinal,
Acording to my knowledge,
DELETE : DML Operation, can RollBack, logs Page Deallocation, logs Record Removal.
TRUNCATE : DDL Operation, can RollBack, logs Page Deallocation, doesn’t logs Record Removal.
and I hope it true.
In case of TRUNCATE its true that it can be Rollbacked if it is executed inside Begin..RollBack Tran. And is same for DELETE also…
If you can, please give a description about getting the deleted records from it log file. I know it might be something related to DB Administration.
Regards,
NandaKumar
Is this something that SQL server behaves weirdly or same holds good with Oracle ? However, from my practice both DDL and DML are dependant on TRANs if used within begin tran…… rollback tran or commit tran
Your every concepts are beneficial to me and i read carefully every day .I specially thanks you.
Thanks in advance
dilip prakash
Hi,
using the truncate in the following way in a transaction it will roll back
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
– Check the data after truncate
SELECT * FROM TruncateTest
– Rollback Transaction
ROLLBACK TRAN
GO
SELECT * FROM TruncateTest
Go
Hi Pinal,
I recently developed a script that can recover the truncated data from the log/ data page.
http://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/
Imran
HI Pinal please refer me a page from your Website where I can Learn about Memory allocations ,issues,resolutions for them .
Also A guy from Microsoft once told me that for my server whatever memory I get on my Server SQL will only use 4 GB’s of it…
can u help me in what Kind,of scenerio this situation may arise.
In case there is any Server Configuration or verion that uses only 4gb ram
FYI:
I have 16 gb ram ,windows 2003(32 bit),sql server standard edition sp2.
would really appreciate your response on the same .Thx in advance
Hi, Kunal
In sqlserver all three action Delete,Truncate and Drop is passible.
Am I right ?
Hi, Kunal
In sqlserver Roll Back in all three action Delete,Truncate and Drop is passible.
Am I right ?
I am Hi Asif this is jai
The Delete and truncate can be rolled back for sure but I would really Like to know if DROP can also be rolled back in case you use it in a transaction clause
Yes. Have you tried it? Everything is rollbacked under a transaction except table variable
Thanks madhi I am agree with you
i have to sql query for insert into two table main and detail.i want to insert into first in main table by store prosedue sp1 and 2nd in detail table by store prosedue sp2 but i want to that if there is in any error occurred at the time of insert into any table then all record inserted into both table is roll back.
please provide code
You need to use a wrapper procedure that has transaction and call other procedures. This way if there is any error, you can rollback
Can we perform truncate on Create command as well ?
No. Why do you want to do this?
Hi Pinal,
Is that same thing happens whe we use bulklogged ? can we rollback a BCP as bulklogged uses the same concept of logging only deallocations of data. ?
[...] Rollback TRUNCATE Command in Transaction TRUNCATE is indeed logged operation and it can be rolled back. If you think this statement is incorrect, I suggest you go and check the blog post above here. [...]