RollBack Truncate Command In SQL Sever

Truncate is used to delete all data in a table.It will delete the data page by page manner why because truncate is fast compare to delete.delete will maintain log and truncate will not maintain log.

Here my quation is If we use Transaction in our code Truncate can be rolled back or not .. If it is rollback what case it will rollback?

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:Truncate is a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed:

We might have listened and read either of following sentence many many times.

“DELETE can be rolled back and TRUNCATE can not be rolled back”.
OR
“DELETE can be rolled back as well as TRUNCATE can be rolled back”.

While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE can not be rolled back using log files in full recovery mode.

DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files. If deallocated data files are overwritten by other data it can be recovered using rollback. There is no guarantee of the rollback in case of TRUNCATE. However, while using T-SQL following code demonstrates that TRUNCATE can be rolled back for that particular session.

BEGIN TRAN
TRUNCATE TABLE TestTable-- Following SELECT will return TestTable emptySELECT *FROM TestTable-- Following SELECT will return TestTable with original dataROLLBACK
SELECT * FROM TestTable

NOTE: DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.