April 5, 2008 at 3:27 am
Its quite a general question.
I have read from the beginning that Truncate cannot be rollbacked but is it really true because now i have read some articles on the net which claim that Truncate CAN be rollbacked like the Delete Statement.
When i tried it myself i found that Truncate is indeed being rollbacked on my instance of SQL SERVER 2005.
Or is it the case that Truncate can be Rollbacked only in specific Versions of SQL SERVER or OS?
Please help me in clearing the confusion.
April 5, 2008 at 6:38 am
Rollback on a TRUNCATE TABLE command works on both SQL Server 2000 and SQL Server 2005. For example:
use tempdb
go
create table dbo.testor(id integer identity)
insert into testor default values
insert into testor default values
insert into testor default values
insert into testor default values
insert into testor default values
select * from testor
/* -------- Data before TRUNCATE / ROLLBACK --------
id
-----------
1
2
3
4
5
*/
begin tran doWhat
truncate table dbo.testor
rollback tran doWhat
select * from testor
/* -------- Data after TRUNCATE / ROLLBACK --------
id
-----------
1
2
3
4
5
*/
drop table dbo.testor
go
April 5, 2008 at 7:50 am
Truncate can be rolled back if wrapped in a explicit transaction by the same session; however, I believe the major difference is that the data pages are deallocated for truncation. If the truncation is committed, you have to do a point-in-time restore, or write the data back into the table from backup, to undo the truncation. If you choose a delete, you can use log reader tools to undo the delete; however, a committed truncate will probably be unavailable for undo via a log reader tool.
more info:
http://www.siusic.com/wphchen/recover-data-using-transaction-log-144.html
April 5, 2008 at 7:53 am
Well said, Adam; thak you for picking me up on this. 🙂
April 7, 2008 at 12:26 am
I've read some articles that say that truncate is not logged, hence cannot be rolled back. The truth is that truncate is a partially-logged operation.
The row 'deletions' are not logged as they would be for a normal delete statement, but the page deallocations are logged. Hence the truncate can be rolled back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2008 at 10:50 pm
Truncate can be rolled back within an explicit transaction just as Adam said... try it on a table that can easily be rebuilt like a Tally table...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2008 at 10:05 am
GilaMonster (4/7/2008)
I've read some articles that say that truncate is not logged, hence cannot be rolled back. The truth is that truncate is a partially-logged operation.The row 'deletions' are not logged as they would be for a normal delete statement, but the page deallocations are logged. Hence the truncate can be rolled back.
Our resident storage engine expert Paul Randal did some testing on this a while back, and described it this way: the truncate activity (which essentially deallocates pages or marks them as "free") itself is not logged. However, for the length of the explicit transaction, exclusive locks are maintained on those deallocated pages, so nothing is allowed to write to those data pages, and a list of these datapages is logged. So, if a rollback should occur, the only thing that needs to happen is to update the (?) GAM to mark those pages as "in use" instead of "free".
Upon commit, those pages are marked as free, so are free to be reused.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 2:01 pm
I believe the source of this is that in Oracle (and possibly other DB's) a truncate CANNOT be rolled back. But in SQL Server, it CAN!
The reason that Oracle cannot be is because all DDL (and for some reason truncate is classified as DDL) force a commit. i.e. Truncate cannot be rolled back since it already commited.
April 9, 2008 at 12:27 am
Matt Miller (4/8/2008)
The truncate activity (which essentially deallocates pages or marks them as "free") itself is not logged.
The changes to the allocation pages are logged, otherwise you couldn't do a point-in-time recovery using tran log backups if you'd done a truncate since the last full backup.
Easy to show. Do this in a test DB that's set to simple recovery mode.
CREATE TABLE [dbo].[Test2](
[id] [int] NULL,
[val] [char](2) NULL
) ON [PRIMARY]
INSERT INTO [Test2]
SELECT column_id, LEFT(NAME,2) FROM sys.columns
CHECKPOINT -- truncates the log
BEGIN TRANSACTION
SELECT * FROM ::fn_dblog(NULL, NULL) -- 2 rows on my test DB
TRUNCATE TABLE [Test2]
SELECT * FROM ::fn_dblog(NULL, NULL)
-- 20 rows, including mods of IAM and PFS and other allocation pages,
-- One PFS change described as (Deallocated 0001:00000382)
-- and some HOBT DDL statements - (Action 2 on HoBt 0x8e:100, partition 0x0, rowset 72057594047234048.)
ROLLBACK TRANSACTION
SELECT * FROM ::fn_dblog(NULL, NULL)
-- 46 rows, the later ones described as compensation (i assume that's the undo of the tran)
-- (COMPENSATION;Allocated 0001:00000382)
-- (COMPENSATION;Action 2 on HoBt 0x8e:100, partition 0x0, rowset 72057594047234048.)
DROP TABLE Test2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 5:02 am
[font="Verdana"]
For more details check out the attached file here. I got this information online, 3-4 months ago. I just saved the information regarding to the difference between Delete and Truncate. I don't know the exact site for this. BTW, this could help you more.
Thanks,
Mahesh
[/font]
MH-09-AM-8694
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply