SQL Server Performance Forum – Threads Archive

How to rollback Truncate operation?

Hi All I have been ask few time this question that how do rollback truncation action which fired with out BEGIN TRAN and COMMIT command.. just fire the commnd (truncate table_name)… I have done some investigation regarding this issue but could not get satisfactory results. please help…
Thanks in advance

I don’t think it’s possible, except if you restore from a backup.

TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

I think Merrill is correct. Without an explicite transaction you’re pretty much lost here. —
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

It is always better to test all Deletes and Truncates in TEST server before applying them in PRODUCTION Server Madhivanan Failing to plan is Planning to fail

Hi Guys I am very glad that answers are given by all senior members… Thanks. But as Satya’s explaintion that data pages are deallocated but data is still exists and extents are marked as empty for reuse. If data is still exists then is there any method we can get back (show) them on same data pages the ones we truncated or marked those extents as full (or not empty) with same data pages. I am try to think that some way to rollback truncate operation cos it is mentioned in BOL that Truncate command can rollback. In this way we can avoid restoration of trucated data from backup.
Please advice… regards

I’ve written a FAQ about that:http://www.sql-server-performance.com/q&a118.asp
—
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks Frank… I have read the article and it is good enough to understand the difference between TRUNCATE and DELETE specially last paragraph. quoted from your article:
"You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state." so could you please explain through practical example????? cos still confuse about how would I rollback the truncate operation from transaction log. thanks again
regards

Just like any other transaction
USE tempdb
CREATE TABLE t (c1 INT)
INSERT INTO t SELECT BINARY_CHECKSUM(OrderID) FROM Northwind..Orders
SELECT *
FROM t
BEGIN TRAN
TRUNCATE TABLE t
COMMIT TRAN
SELECT *
FROM t
INSERT INTO t SELECT BINARY_CHECKSUM(OrderID) FROM Northwind..Orders
BEGIN TRAN
TRUNCATE TABLE t
ROLLBACK TRAN
SELECT *
FROM t
DROP TABLE t
—
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks Frank… But my question was if we fired TRUNCATE command without BEGIN TRAN it will logged this operation in transaction log as deallocation of data pages(as mentioned in you article).
Then can we rollback the TRUNCATE operation in this situation. if yes then how? if not then wny not? thanks again….
regards

Ah, I see now. Well, I’m guessing now. I don’t know that for sure… TRUNCATE marks some storage space as available for reuse. If you notice quickly after the TRUNCATE that it has been fired, and try to rollback the operation with a third-party tool, the space might have not been occupied by other data yet. The longer you wait, the more likely it is, that the physical data has been overwritten. Hope someone else can either verify or rectify my guess. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

quote:Originally posted by sonnysingh Thanks Frank… But my question was if we fired TRUNCATE command without BEGIN TRAN it will logged this operation in transaction log as deallocation of data pages(as mentioned in you article).
Then can we rollback the TRUNCATE operation in this situation. if yes then how? if not then wny not? thanks again….
regards

It’s not possible. If you run any of the below statements without a begin tran then SQL Server will automatically run an implicit transaction (unless you had IMPLICIT_TRANSACTIONS set to OFF). In other words, executing "truncate table mytable" is equivalent to :
begin tran
truncate table mytable
commit tran
In other words, the truncate table statement was committed and cannot be rolled back. Now, whether there is a third-party tool that lets you do this I honestly don’t know but I’d question the wisdom of using any such tool (if it existed) since the point of having all of this transaction processing in place is to maintain consistency and you might end up with an inconsistent set of data. It would be easier and cheaper to restore the database – or restore it to another location from which you can access this one table and then insert that table’s data into the original table. Here’s the list of statements to which IMPLICIT_TRANSACTIONS applies: ALTER TABLE FETCH REVOKE
CREATE GRANT SELECT
DELETE INSERT TRUNCATE TABLE
DROP OPEN UPDATE
Karl Grambow www.sqldbcontrol.com

Call me stupid, but if it must be reversible through a ROLLBACK TRANSACTION, then why are we not suggesting … BEGIN TRAN
DELETE FROM mytable IF …
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
DBCC whatever it is to reset the identity column
END

Hi Adriaan, that would be the logical thing to do in the first place. But, if I understood the question correctly, the original poster wanted to know if it was possible to rollback a truncate table transaction that had already been committed, due to the fact that an implicit transaction was run by leaving out the explicit BEGIN TRAN.

quote:
…how do rollback truncation action which fired with out BEGIN TRAN and COMMIT command?

Of course, I might be wrong and have completely misunderstood the original question[:I] Karl Grambow www.sqldbcontrol.com

Adriaan, there are times when it is desireable to use TRUNCATE instead of the DELETE script you posted. The primary ones are log space and duration of execution. DELETE is a fully-logged operation, and if you have a 2GB table you may not want to have that operation logged due to log space and the time it would take to complete. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I do believe third party tools can recover the data from a TRUNCATE, but you are at the mercy of the pages having been reassigned. Personally I would restore the database to a different name and BULK insert the data from that copy. If you need modifications to the data from the point of the last restore to the time of the TRUNCATE action, use a log recovery tool to REDO the DML statements against that table from the production database’s online xaction log and any saved xaction log backups since the full backup used to recover the table. SQLGuru

]]>