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
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.
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. ?
if has deleted datas in our server no backup then how to take that datas
Hi All,
One little help plz..
Declare @T table(A int, B char(1))
insert into @T values(1,’A’),(15,’A’),(13,’B’),(1,’A’)
BEGIN TRY
BEGIN TRAN
delete from @T where B=’A’
insert into @T values(12,’A’)
insert into @T values(‘2k’,’A’)
insert into @T values(35,’A’)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
select * from @T
———————————————————-
I want to ROLLBACK the whole transaction so that in any case of error or uncommitted transactions , I can have the old data at least.
Dear All,
Can you clear me about Delete Command in sql server?
if i delete the data from the table then how can i rollback that row?
Because as we read about delete command : we can rollback the deleted data but not in Truncate Command.
Please clarify ….
Thanks
Hi Pinal,
Your blogs are always helpful and want to say thanks for that. I have got a problem in replication
I want to use truncate table statement in a replication environment.But unable to use it as it’s giving error. Currently the replication is a transitional replication only.
My database is SQL SERVER 2012 Standard Edition.
I can not go for snapshot replication due to some restrictions.
Can you please help me with this problem.
Hi Pinal,
I am Using SQL Server 2005
Suppose we have Table Tutorials_tbl
Create Table Tutorials_tbl (tutorial_ID INT)
We have inserted the 3 values
INSERT INTO dbo.Tutorials_tbl
Select ‘1’, Union All
Select ‘2’,Union All
Select ‘3’,
Select * from Tutorials_tbl
tutorial_ID
1
2
3
Delete From Tutorials_tbl (DELETE command will delete all the data in the table)
Truncate Table Tutorials_tbl (TRANCATE command will delete all the data in the table)
If we had use the TRANCATE or DELETE either of the command will have same effect in the Table Tutorials_tbl (Command will delete all the data in the table)
Select * from Tutorials_tbl
tutorial_ID tutorial_title tutorial_author submission_date
If we had use the TRANCATE or DELETE either of the command by using BEGIN TRANSACTION (Like below)
For TRANCATE
Begin Tran
Truncate Table Tutorials_tbl
Go
For DELETE
Begin Tran
Delete From Tutorials_tbl
Go
We had Recovered the Data by Below command using for both TRANCATE OR for DELETE
Rollback tran
Then what is the difference between them to recover the data? Why we say that Delete can be rolled back if we can’t rollback if we are not using Transaction? Please tell me command to get the data back. Please clear my doubts…
Would be waiting for your Reply.
Thanks
Brijesh
Hello dear, My question is how can i get my deleted or updated data from table in sql server using rollback functionality.