SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed

You 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”.

As soon as above sentence is completed, someone will object it saying either TRUNCATE can be or can not be rolled back. Let us make sure that we understand this today, in simple words without talking about theory in depth.

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.

Let us understand this concept in detail.

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.

First create test table which some data. Afterwards run following T-SQL code in Query Editor and test the effect of TRUNCATE on created test table.
BEGIN TRAN
TRUNCATE TABLE
TestTable
-- Following SELECT will return TestTable empty
SELECT *
FROM TestTable
-- Following SELECT will return TestTable with original data
ROLLBACK
SELECT
*
FROM TestTable

Summary : 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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Mirrored Backup Introduction and Explanation
Next Post
SQL SERVER – Difference Between Quality Assurance and Quality Control – QA vs QC

Related Posts

36 Comments. Leave new

  • Sagar Vernekar
    April 16, 2010 11:32 am

    it was nice thanks dear

    Reply
  • Rishikesh Kumar
    June 11, 2010 4:07 pm

    SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

    This SYSDATETIME() is not get a proper value and sql server 2005 show error message.

    Reply
  • suppose, i have one table with four columns(name,id,city,salary)

    in name column,i’l have raj,ravi,ashok,poonam,pooja,etc and i want to add ‘s with every name. So,what can i do.

    Reply
  • Hi
    Pinaldave

    I am using Sql Server 2008
    accedently data delete from table but I have no Transactional Backup and my database in full recovery .
    I have Full Backup of last 1 Month .so plz suggest me how to recover data from table …plz it is very urgent ….

    Reply
  • cn u send me kuries with answer on my email [email removed]

    Reply
  • hi…….once transaction is committed,it can not be rolled back for both delete and truncate

    Reply
  • If we don’t use BEGIN TRANSACTION statement but we deleted or truncated a table. It is possible to rollback and I think using .ldf.
    I don’t know how Can anyone help Me on this???

    Great Thanks in Advance!

    Reply
  • very nice explanation

    Reply
  • If your database is in full recovery mode you can recover data either by truncated, deleted or dropped command

    Complete Step by Step Article is here

    Reply
  • Nice and useful information in some real time cases.

    Reply
  • Mohammad farheen
    December 14, 2019 5:00 am

    Hie I am.working on certain project..I jst gave truncate table tablename is it possible to rollback my data because my data is lost

    Reply

Leave a Reply

Menu