Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Haunting TSQL Tuesday Tale

Time for the ghouls and goblins to come out of the woodwork once again for another tale of deception and tyranny.  OK maybe not tyranny but definitely a haunting tale is to be told this month while trick-or-treaters plan their routes and mend their costumes.  We have SQueeLing experts suiting up for the adult party known as TSQL Tuesday.

This month the vampires, ghosts, and zombies are all breaking it down at Sankar Reddy’s place (Blog | Twitter).  The ghoulish stories to be shared are to be myths from the underSQLworld.

Truncating a Table is Unrecoverable

Have you heard this one in the past?  If you truncate a table, you cannot undo the action like you can with a delete.  What is the difference between the two methods to remove data from a table?

When you truncate a table, the pages are deallocated with minimal locking of the resources.  When you use the delete statement, the data is deleted row-by-row with more locks.  Everybody knows that a delete can be rolled back.  Many people believe that a Truncate cannot be rolled back.

Well, at this point we really need a table to test.  So let’s create a table and perform a few simple tests.

SELECT TOP 1000000
		RollID       = IDENTITY(INT,1,1)
	INTO dbo.TruncTabRollback
	FROM Master.dbo.SysColumns t1,
		Master.dbo.SysColumns t2 
 
ALTER TABLE dbo.TruncTabRollback
	ADD PRIMARY KEY CLUSTERED (RollID)

With the test table ready to go, we will begin with the baseline demonstration on the Delete command.  Here is the script to demonstrate that a Delete can be rolled back (but we already knew that).

BEGIN TRAN delrollback
 
DELETE TruncTabRollback
 
SELECT COUNT(*) AS PreRollBack
	FROM TruncTabRollback
 
ROLLBACK TRAN delrollback
 
SELECT COUNT(*) AS PostRollBack
	FROM TruncTabRollback

Pretty simple and straight forward – baseline is proven with that script.  The premise that a Delete can be rolled back has been established.  Now, how about that truncate statement?  For the truncate statement, I will use the same simple script, substituting the appropriate commands in this script.

BEGIN TRAN delrollback
 
TRUNCATE TABLE TruncTabRollback
 
SELECT COUNT(*) AS PreRollBack
	FROM TruncTabRollback
 
ROLLBACK TRAN delrollback
 
SELECT COUNT(*) AS PostRollBack
	FROM TruncTabRollback

When this script is executed, we see that indeed the data is recoverable.  Here are the results I get from the truncate and associated rollback script.

Conclusion

I think this is one of those myths that is frequently floated throughout the SQL world due to another myth that is related to the Truncate statement.  It is often explained that you can’t recover a truncate because the truncate is not logged.  That is also obviously wrong (if it weren’t logged, how could you recover the table without a restore?).  The myth about Truncate being non-logged has already been debunked (as has this one – in a different series) by Paul Randal in his Myth-A-Day series.  The key to this is that the Truncate is logged and that you wrap the command in a transaction – and only commit when you are ready.

Comments

Posted by Steve Jones on 12 October 2010

Nice one, I'd forgotten about this and I was thinking it would roll back, but I've had had to test it if I was working on this.

Posted by Jason Brimhall on 12 October 2010

Thanks Steve.

Posted by Dukagjin Maloku on 13 October 2010

Yep it works nice, hope that the BEGIN TRAN .... is active and executed at the beginning of T-SQL code!

I don't know how many DBAs practice to work with BEGIN TRAN...!?

I use it for the important updates!

Posted by Michael.Beeby on 14 October 2010

Please be aware TRUNCATE will reset the seed.  If you wish the seed value to remain, use DELETE.

Posted by Michael.Beeby on 14 October 2010

(NB: Above comment does not apply in this case as the ROLLBACK will reapply - sorry.)

Posted by André Lozeau on 14 October 2010

the thougth that TRUNCATE cannot be rolled back probably comes from Oracle, where the TRUNCATE statement is automatically commited

Posted by Jason Marshall on 14 October 2010

Nice! I was with the masses, thinking TRUNCATE couldn't be rolled back...

Posted by Kenneth Wymore on 14 October 2010

Wow, I have worked with SQL for 9 years and did not know you could roll back a truncate statement. Just goes to show how powerful myths can be! Thanks for the eye-opener.

Posted by Charles Kincaid on 14 October 2010

There is a difference between "Unrecoverable" and "Non-reversable".  Can you roll back a DROP TABLE?

Yet if you use the Red Gate tools you should be able to "recover" from either DELETE, TRUNCATE, or DROP.

Posted by Jason Brimhall on 14 October 2010

Dukagjin Maloku - Thanks

Posted by Jason Brimhall on 14 October 2010

Michael.Beeby - Valid point still.  And thanks for also pointing out that the rollback will put the see value back.

Posted by Jason Brimhall on 14 October 2010

André Lozeau - quite possible true.  Thanks for the input.

Posted by Jason Brimhall on 14 October 2010

Jason and Kenneth - I believe you.  I have had this hurt me in interviews because the interviewer believed that a truncate could not be rolled back.

Posted by Jason Brimhall on 14 October 2010

Charles Kincaid - thanks for the insight.

Posted by zainu deen on 16 October 2010

Truncate does log some data in transaction log file.Although it doesn't log the individual records deleted

but it does log the modified data page info(just allocation and deallocation) in transaction log .So you are correct the statement truncate is a non logged operation is just a myth.

Posted by Ken Lee-263418 on 17 October 2010

The author is mixing apples and oranges and coming up with grapes.

It is impossible to recover data from a table that has been truncated. That is not myth, that is fact.

That is apples.

It is possible to undo a truncated table if you roll back the transaction, his "test" proves that.

That is oranges.

Saying these two processes are recovery processes are grapes.

Now that you can recover data that is deleted by restoring the table just before the delete is well documented. You can also restore a db backup and apply the changes made just before the delete and truncate.

I don't know if you can apply the changes made after the truncate. If that is the case then you truly have an example of recovering the data from the log records. You just need to ignore every transaction made in that time period throughout your DB.

If that is unacceptable, you can't recover from either the delete or truncate.

Posted by Jeff Moden on 17 October 2010

And yet, Ken... everyone understood what was being said. ;-)

Leave a Comment

Please register or log in to leave a comment.