Miss-understanding of Truncate Command
Introduction
Recently I been interviewing for senior level DBA for our company. One of the technical questions that I asked all the candidates was "Can TRUNCATE command be roll backed". To my surprise, none of the candidate said Yes. I believe there is a miss understanding that DELETE can be rollbacked but not TRUNCATE command. In this article, I want to show that even TRUNCATE command is logged in transaction log and can be rollbacked.
Let's create a new database for the demonstration purpose.
USE MASTER
GO
CREATE DATABASE [test]
GO
USE [test]
GO
Create a test table and populate it.
CREATE TABLE VTTEST(id INT IDENTITY, data char(10))
GO
INSERT INTO VTTEST(DATA) VALUES('TEST')
GO 10
CHECKPOINT
Issue the above command couple of times to clear the log.
 Caution |
|---|
| Before I begin I would like to point out fn_dblog() is an undocumented function introduced in SQL Server 2005. This function is used to return active (or un-truncated) part of transaction log file. To read the transaction log we need to use a function fn_dblog(). Undocumented command is not supported by Microsoft and should not be used on production database in any case. |
Implicit Transaction
Open a new query window and issue following command
TRUNCATE TABLE vttest
Open a new query window and execute the following
SELECT
[Current LSN], [Operation], [Context],
[Transaction ID], [Transaction Name], [Description]
FROM fn_dblog (NULL, NULL)
Current LSN
Operation
Context
Transaction Name
Description
00000025:0000007e:0001
LOP_BEGIN_XACT
LCX_NULL
TRUNCATE TABLE
TRUNCATE TABLE;0x01050000000000051500000079762b0f9908d06414787b49a42d0000
00000025:0000007e:0002
LOP_LOCK_XACT
LCX_NULL
NULL
00000025:0000007e:0003
LOP_MODIFY_ROW
LCX_IAM
NULL
00000025:0000007e:0004
LOP_MODIFY_ROW
LCX_PFS
NULL
Deallocated 0001:000000b1
00000025:0000007e:0005
LOP_MODIFY_ROW
LCX_PFS
NULL
Deallocated 0001:000000b2
00000025:0000007e:0006
LOP_INVALIDATE_CACHE
LCX_NULL
NULL
cache invalidation (cache: IAMPageRangeCache, keys: allocUnitId: 72057594045792256).
00000025:0000007e:0007
LOP_COUNT_DELTA
LCX_CLUSTERED
NULL
Action 0 (HOBTCOUNT) on rowset 72057594040549376. Leaf page count: 1, Reserved page count: 2, Used page count: 2
00000025:0000007e:0008
LOP_COUNT_DELTA
LCX_CLUSTERED
NULL
Action 1 (ROWSETCOUNT) on rowset 72057594040549376. Row count: 20.
00000025:0000007e:0009
LOP_COUNT_DELTA
LCX_CLUSTERED
NULL
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594040549376. Column Id: 1, mod count: 360
00000025:0000007e:000a
LOP_COUNT_DELTA
LCX_CLUSTERED
NULL
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594040549376. Column Id: 2, mod count: 360
00000025:0000007e:000b
LOP_HOBT_DDL
LCX_NULL
NULL
Action 2 (ALTER_HOBT) on HoBt 0x28:100, partition 0x0, rowset 72057594040549376.
00000025:0000007e:000c
LOP_MODIFY_ROW
LCX_CLUSTERED
NULL
00000025:0000007e:000d
LOP_HOBT_DDL
LCX_NULL
NULL
Action 2 (ALTER_HOBT) on HoBt 0x28:100, partition 0x0, rowset 72057594040549376.
00000025:0000007e:000e
LOP_MODIFY_ROW
LCX_CLUSTERED
NULL
00000025:0000007e:000f
LOP_MODIFY_ROW
LCX_CLUSTERED
NULL
00000025:0000007e:0010
LOP_COMMIT_XACT
LCX_NULL
NULL
As you can see in the above table that TRUNCATE command is executed with in implicit transaction (LOP_BEGIN_XACT,LOP_COMMIT_XACT) and is logged in transactional log as well.
Explicit Transaction
Let's see what happens when TRUNCATE table is issued with an explicit transaction on. Execute CHECKPOINT command couple of times to clear the log the execute following command
INSERT INTO VTTEST(DATA) VALUES('TEST')
GO 10
CHECKPOINT
BEGIN TRAN VTTEST
TRUNCATE TABLE VTTEST
ROLLBACK
Current LSN
Operation
Context
Transaction ID
Transaction Name
Description
00000025:000000da:0001
LOP_BEGIN_CKPT
LCX_NULL
0000:00000000
NULL
00000025:000000db:0001
LOP_XACT_CKPT
LCX_BOOT_PAGE_CKPT
0000:00000000
NULL
00000025:000000dc:0001
LOP_END_CKPT
LCX_NULL
0000:00000000
NULL
log_minRecoveryLsn 00000025:000000da:0001;
log_replbeginlsn 00000000:00000000:0000;
log_replnextlsn 00000000:00000000:0000;
log_distbackuplsn 00000000:00000000:0000;
log_distlastlsn 00000000:00000000:0000
00000025:000000dd:0001
LOP_BEGIN_XACT
LCX_NULL
0000:0000043e
VTTEST
VTTEST;
0x01050000000000051500000079762
b0f9908d06414787b49a42d0000
00000025:000000dd:0002
LOP_LOCK_XACT
LCX_NULL
0000:0000043e
NULL
00000025:000000dd:0003
LOP_MODIFY_ROW
LCX_IAM
0000:0000043e
NULL
00000025:000000dd:0004
LOP_MODIFY_ROW
LCX_PFS
0000:0000043e
NULL
Deallocated 0001:000000b1
00000025:000000dd:0005
LOP_MODIFY_ROW
LCX_PFS
0000:0000043e
NULL
Deallocated 0001:000000b2
00000025:000000dd:0006
LOP_INVALIDATE_CACHE
LCX_NULL
0000:0000043e
NULL
cache invalidation (cache: IAMPageRangeCache, keys: allocUnitId: 72057594045792256).
00000025:000000dd:0007
LOP_COUNT_DELTA
LCX_CLUSTERED
0000:00000000
NULL
Action 0 (HOBTCOUNT) on rowset 72057594040549376.
Leaf page count: 1, Reserved page count: 2, Used page count: 2
00000025:000000dd:0008
LOP_COUNT_DELTA
LCX_CLUSTERED
0000:00000000
NULL
Action 1 (ROWSETCOUNT) on rowset 72057594040549376.
Row count: 20.
00000025:000000dd:0009
LOP_COUNT_DELTA
LCX_CLUSTERED
0000:00000000
NULL
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594040549376.
Column Id: 1, mod count: 410
00000025:000000dd:000a
LOP_COUNT_DELTA
LCX_CLUSTERED
0000:00000000
NULL
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594040549376.
Column Id: 2, mod count: 410
00000025:000000dd:000b
LOP_HOBT_DDL
LCX_NULL
0000:0000043e
NULL
Action 2 (ALTER_HOBT) on HoBt 0x28:100, partition 0x0, rowset 72057594040549376.
00000025:000000dd:000c
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
00000025:000000dd:000d
LOP_HOBT_DDL
LCX_NULL
0000:0000043e
NULL
Action 2 (ALTER_HOBT) on HoBt 0x28:100, partition 0x0, rowset 72057594040549376.
00000025:000000dd:000e
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
00000025:000000dd:000f
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
00000025:000000dd:0010
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
COMPENSATION
00000025:000000dd:0011
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
COMPENSATION
00000025:000000dd:0012
LOP_HOBT_DDL
LCX_NULL
0000:0000043e
NULL
COMPENSATION;Action 2 (ALTER_HOBT) on HoBt 0x28:100,
partition 0x0, rowset 72057594040549376.
00000025:000000dd:0013
LOP_MODIFY_ROW
LCX_CLUSTERED
0000:0000043e
NULL
COMPENSATION
00000025:000000dd:0014
LOP_HOBT_DDL
LCX_NULL
0000:0000043e
NULL
COMPENSATION;Action 2 (ALTER_HOBT) on HoBt 0x28:100,
partition 0x0, rowset 72057594040549376.
00000025:000000dd:0015
LOP_MODIFY_ROW
LCX_PFS
0000:0000043e
NULL
COMPENSATION;Allocated 0001:000000b2
00000025:000000dd:0016
LOP_MODIFY_ROW
LCX_PFS
0000:0000043e
NULL
COMPENSATION;Allocated 0001:000000b1
00000025:000000dd:0017
LOP_MODIFY_ROW
LCX_IAM
0000:0000043e
NULL
COMPENSATION
00000025:000000dd:0018
LOP_ABORT_XACT
LCX_NULL
0000:0000043e
NULL
The red color part are the log entries for
BEGIN TRAN VTTEST
TRUNCATE TABLE VTTEST
and the green color are the log entries for
ROLLBACK
As you can see in the red section the data page got deallocated and in the green section the data pages are getting allocated back up on rollback.
Conclusion
From this it is clear that TRUNCATE Command is a logged operation and can be rollbacked.