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 (http://blog.SQLAuthority.com)

About these ads

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

  1. i have table with the data like below:

    ids_cloumn
    ========================
    1 , 2 , 4 , 23 , 12 , 11 , 9 , 8 , 7 , 10 ,
    1 , 3 , 4 , 21 , 16 , 12 , 19 , 10 ,
    2 , 3 , 6 , 24 , 15 , 18 , 9 , 8 ,
    3 , 5 , 8 , 27 , 14 , 19 , 39 , 7 , 13 ,
    1 , 7 , 9 , 28 , 19 , 11 , 29 , 8 ,

    i am having a list of nos with me (coming from another query)
    ex1: 1, 3 & 23
    ex2 : 1, 6, 34 & 23

    now i want to retrieve the list of rows which is having any of the nos.

    is there any other way with out using “LIKE %”

    If i am using like i need to write so many OR conditions based on the inputs.

    thanks

  2. Hi Andrew,

    Thanks for comment. I have tested it earlier and delete can be rolled back using log files even though it is committed. You will have to use point of time restore using log files. However, Truncate can not be restored using log files. I tried to convey this message in title of this post.

    I appreciate your regular participation in this blog. Your comments are always valuable.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    • Hi Pinal,

      I am the regular reader of ur site.where i have some doubt and confusion regarding the rollback for delete after commiting it.

      Can u please explain with an example,so that it could be useful for many folks like me.

  3. Hi Pinal,

    Thanks for replying. I feel confuse when you mention roll back using log file together with the “Roll Back” statement. Roll back statement only rolls back the current open transaction in current database session. It can not roll back a committed transaction.

    Roll back using log file however will roll back many transactions. we can not roll back a specific delete that way. Everthing done after the delete will also get rolled back. I got the impression from your post that we can roll back a specific delete statement using log file.

    As you point out roll back using log file is to restore a database to a point of time

  4. Andrew,

    Point in time restore using log file can not roll back TRUNCATE if the data file which is deallocate is over-written. That is the difference.

    That is why I used last statement in post “TRUNCATE may or may not be recovered always from log files.”

    Kind Regards,
    Pinal Dave ( http://www.SQLAuthority.com)

  5. Pinal,

    Sorry for being stubborn. But as far as I know in a point in time restore. A full database backup has to be be restored first and then the log backup is actually used to “Roll Forward” the database to the point in time when the data deleted or turncated were still exist. So it would not be a problem whether or not the data file got over written because it will be re-created in database restore anyway.

    Maybe I miss unstand something but if you can share an example of using log file to “Roll Back” a commited DELETE then it can make things clear. I believe it would be something a lot of DBAs like know about

    Appreciated
    Andrew

  6. I’m completely agree with Andrew. Truncate table not just safe for rollback in transaction scope. It still possible undo truncation of table using restore log and stopat.

    There just reproduction code:

    – STEP 1. Creating DB
    SET NOCOUNT ON
    CREATE DATABASE TEST
    GO
    ALTER DATABASE TEST SET RECOVERY FULL
    USE TEST
    GO

    — STEP 2. Creating table with some data (100 rows)
    CREATE TABLE data (f1 INT IDENTITY)
    GO
    INSERT data DEFAULT VALUES
    GO 100

    – STEP 3. Performing full backup
    BACKUP DATABASE TEST TO DISK=’c:\test_full.bak’ WITH INIT
    GO

    – STEP 4. Performing log backup
    DECLARE @date DATETIME

    – variant1: Date mark just before truncation
    SET @date = DATEADD(s, 0, GETDATE())

    – variant2: Date mark after truncation
    – SET @date = DATEADD(s, 1, GETDATE())

    TRUNCATE TABLE data
    WAITFOR DELAY ’00:00:03′
    BACKUP LOG TEST TO DISK=’c:\test_log.bak’ WITH INIT

    – STEP 5. Restoring Test DB.
    USE MASTER
    RESTORE DATABASE TEST FROM DISK=’c:\test_full.bak’ WITH NORECOVERY, REPLACE
    RESTORE LOG TEST FROM DISK=’c:\test_log.bak’ WITH RECOVERY, STOPAT=@date
    GO
    USE TEST
    GO
    select COUNT(*) AS CNT FROM data
    go
    USE MASTER
    DROP DATABASE TEST

    p.s. Please, delete my previous comment :))

  7. Acutally there are third party transaction Log reading Tools that are able to read transaction logs and allow you undone commited statements or transactions. Tuncate satement may or may not be undone using such tools. SQL server itselft does not provide this capability.

    Siusic Dot Com

  8. Thank you!

    I’m understanding now. So, To make a rollback over an alter data. I need to restore the last full backup and next the differential and log backup before to make the change?

    I have any other way to rollback the sentence update?

  9. @Raj,

    Take transactional backup, Then try to restore database using Stop at function, give the time when you think the deleted record was available, and database will be restored till that time, and the record will be available. This process will create a new database. Be careful dont over write the old database.

    Hope this helps,
    Imran.

  10. Hi Folks,

    I have a different observation on this rolling back / recovering the data lost by truncate. The example provided by Alexander is perfect. But here the data truncated is not exactly recovered by restoring the log file to a point before truncate. We are taking a full backup after inserting 100 records, and then truncating the table. So restoring the full backup took care of retrieving all records not the transaction log.

    I have a different case to prove that the data lost due to a TRUNCATE can be recovered, but not 100%. As storage engine only records the deallocation of pages in log, and releases the pages and its corresponding extent for allocation there is definite possibility for data loss. The percentage of data loss depends on how soon we take a log backup and the new requests for page allocation. My simple guess is the transaction log backup we are taking itself can grab those deallocated pages of TRUNCATE and cause data loss. To prove this I have made little changes to Alexander’s code as below. I have added a INSERT statement (highlighted in ‘==’) after taking full backup. These inserts are logged in only transaction log, not backedup during full backup.

    – STEP 1. Creating DB
    SET NOCOUNT ON
    CREATE DATABASE TEST
    GO
    ALTER DATABASE TEST SET RECOVERY FULL
    USE TEST
    GO

    – STEP 2. Creating table with some data (100 rows)
    CREATE TABLE data (f1 INT IDENTITY)
    GO

    INSERT data DEFAULT VALUES
    GO 100

    – STEP 3. Performing full backup
    BACKUP DATABASE TEST TO DISK=’c:\test_full.bak’ WITH INIT
    GO

    –The above full backup will guarantee the first hundred recovery.

    –===================
    INSERT data DEFAULT VALUES
    GO 100
    –====================
    – STEP 4. Performing log backup
    DECLARE @date DATETIME

    – variant1: Date mark just before truncation
    SET @date = DATEADD(s, 0, GETDATE())

    – variant2: Date mark after truncation
    –SET @date = DATEADD(s, 1, GETDATE())

    TRUNCATE TABLE data
    WAITFOR DELAY ’00:00:03′
    –This log backup now has all the 100 records inserted after full backup
    –making total count to 200 records in the table
    BACKUP LOG TEST TO DISK=’c:\test_log.bak’ WITH INIT

    – STEP 5. Restoring Test DB.
    USE MASTER
    RESTORE DATABASE TEST FROM DISK=’c:\test_full.bak’ WITH NORECOVERY, REPLACE
    RESTORE LOG TEST FROM DISK=’c:\test_log.bak’ WITH RECOVERY, STOPAT=@date
    GO
    USE TEST
    GO
    –This will return count less than 200
    select COUNT(*) AS CNT FROM data
    go
    USE MASTER
    DROP DATABASE TEST

    This test gives us a different count every time which confirms a data loss. I am glad to see this forum and will appreciate your feed back.

  11. Truncate can be very much rolled back when it is used in a transaction.

    –select * from emp

    begin transaction

    truncate table emp

    –rollback transaction

    above is the code snap short please try and let me know.

  12. Pingback: SQL SERVER - Time Out Due to Executing DELETE on Large RecordSet Journey to SQL Authority with Pinal Dave

  13. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

  14. Hey Guys,
    I’m not sure if modification I have done are correct or not but I’m able recover all the rows brefore the truncate statement. I just made the wait time little longer…

    –STEP 1. Creating DB
    SET NOCOUNT ON
    CREATE DATABASE TEST
    GO
    ALTER DATABASE TEST SET RECOVERY FULL
    USE TEST
    GO

    –STEP 2. Creating table with some data (100 rows)
    CREATE TABLE data (f1 INT IDENTITY)
    GO

    INSERT data DEFAULT VALUES
    GO 100
    select COUNT(*) AS CNT,’1st 100′ AS Remark FROM data
    –STEP 3. Performing full backup
    BACKUP DATABASE TEST TO DISK=’D:\test_full.bak’ WITH INIT
    GO

    –The above full backup will guarantee the first hundred recovery.

    –===================
    INSERT data DEFAULT VALUES
    GO 100
    select COUNT(*)AS CNT,’2nd 100 AFTER full backup’ AS Remark FROM data
    –====================
    –STEP 4. Performing log backup
    DECLARE @date DATETIME

    –variant1: Date mark just before truncation
    SET @date = DATEADD(s, 0, GETDATE())
    select @date as TimeOfRB

    –variant2: Date mark after truncation
    –SET @date = DATEADD(s, 1, GETDATE())

    TRUNCATE TABLE data
    WAITFOR DELAY ’00:00:10′
    select COUNT(*) AS CNT,’trun’ AS Remark FROM data
    –This log backup now has all the 100 records inserted after full backup
    –making total count to 200 records in the table
    BACKUP LOG TEST TO DISK=’D:\test_log.bak’ WITH INIT

    –STEP 5. Restoring Test DB.
    USE MASTER
    RESTORE DATABASE TEST FROM DISK=’D:\test_full.bak’ WITH NORECOVERY, REPLACE
    RESTORE LOG TEST FROM DISK=’D:\test_log.bak’ WITH RECOVERY, STOPAT=@date
    GO
    USE TEST
    GO
    –This will return count less than 200
    select COUNT(*)AS CNT ,’last’ AS Remark FROM data
    go
    USE MASTER
    DROP DATABASE TEST

  15. Hi

    I have tested the pinal dave post.. Truncate can be rollback whether the database is Full recovery model or simple recovery model…

    Still am not clear please explain me. I am using SQL SERVER 2008

    thanks
    Raj…..-)

  16. Sir

    I have one table (Example – table name)

    In that table two fileds is there 1) slno – int, 2)name – varchar(50)

    I entered the records

    slno Name

    1 a
    2 b
    3 c

    First I Delete one record using Delete Command

    delete from example where slno =’1′

    after that

    truncate the table using truncate command

    truncate table example

    Now

    I roll back record of 1

    how can i?

    i try this but i can’t? Please help me

  17. 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.

  18. 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 ….

  19. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

  20. 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!

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s