(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list.)
Today's myth is very persistent, so it's high time it was debunked with a nice script to prove it too!
Myth #19: a TRUNCATE TABLE operation is non-logged.
FALSE
There is no such thing as a non-logged operation in a user database. The only non-logged operations that SQL Server performs are those on the version store in tempdb.
A TRUNCATE TABLE operation does a wholesale delete of all data in the table. The individual records are not deleted one-by-one, instead the data pages comprising the table are simply deallocated. The allocations are unhooked from the table and put onto a queue to be deallocated by a background task called the deferred-drop task. The deferred-drop task does the deallocations instead of them being done as part of the regular transaction so that no locks need to be acquired while deallocating entire extents. Before SQL Server 2000 SP3 (when this process was put into SQL Server), it was possible to run out of memory while acquiring locks during a TRUNCATE TABLE operation.
Here's an example script:
CREATE DATABASE TruncateTest;
GO
USE TruncateTest;
GO
ALTER DATABASE TruncateTest SET RECOVERY SIMPLE;
GO
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1c1 on t1 (c1);
GOSET NOCOUNT ON;
GOINSERT INTO t1 DEFAULT VALUES;
GO 1280CHECKPOINT;
GO
The database is in the SIMPLE recovery mode so the log clears out on each checkpoint (for simplicity – ha ha :-)
Wait for a minute or so (there may be some ghost record cleanup that occurs) and check how many rows are in the log:
SELECT COUNT (*) FROM fn_dblog (NULL, NULL);
GO
If you don't get a result of 2, do another checkpoint and check the log record count again until it comes back at 2. Now the database is completely quiescent and any new log records are from stuff we're doing. Now we'll do the truncate:
TRUNCATE TABLE t1;
GOSELECT COUNT (*) FROM fn_dblog (NULL, NULL);
GO
I get back a result of 541 log records – clearly the operation is not non-logged, but it's clearly also not deleting each record – as I inserted 1280 records. If we look in the log we'll see:
SELECT
[Current LSN], [Operation], [Context],
[Transaction ID], [AllocUnitName], [Transaction Name]
FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation Context Transaction ID AllocUnitName Transaction Name
———————- —————— —————— ————– ————————– —————-
00000081:000001a6:0016 LOP_BEGIN_CKPT LCX_NULL 0000:00000000 NULL NULL
00000081:000001a9:0001 LOP_END_CKPT LCX_NULL 0000:00000000 NULL NULL
00000081:000001aa:0001 LOP_BEGIN_XACT LCX_NULL 0000:00001072 NULL TRUNCATE TABLE
00000081:000001aa:0002 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0003 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0004 LOP_LOCK_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0005 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysallocunits.clust NULL
00000081:000001aa:0006 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsets.clust NULL
00000081:000001aa:0007 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:0008 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:0009 LOP_COUNT_DELTA LCX_CLUSTERED 0000:00000000 sys.sysrowsetcolumns.clust NULL
00000081:000001aa:000a LOP_HOBT_DDL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:000b LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:000c LOP_MODIFY_COLUMNS LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:000d LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:000e LOP_MODIFY_HEADER LCX_PFS 0000:00000000 Unknown Alloc Unit NULL
00000081:000001aa:000f LOP_SET_BITS LCX_PFS 0000:00000000 sys.sysserefs.clust NULL
00000081:000001aa:0010 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:0011 LOP_MODIFY_ROW LCX_SCHEMA_VERSION 0000:00000000 sys.sysobjvalues.clst NULL
00000081:000001aa:0012 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysallocunits.clust NULL
00000081:000001aa:0013 LOP_INSERT_ROWS LCX_CLUSTERED 0000:00001072 sys.sysserefs.clust NULL
00000081:000001aa:0014 LOP_HOBT_DDL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0015 LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.sysrowsets.clust NULL
00000081:000001aa:0016 LOP_IDENT_SENTVAL LCX_NULL 0000:00001072 NULL NULL
00000081:000001aa:0017 LOP_MODIFY_ROW LCX_CLUSTERED 0000:00001072 sys.syscolpars.clst NULL
00000081:000001aa:0018 LOP_COMMIT_XACT LCX_NULL 0000:00001072 NULL NULL
00000081:000001b0:0001 LOP_BEGIN_XACT LCX_NULL 0000:00001073 NULL DeferredAllocUnitDrop::Process
00000081:000001b0:0002 LOP_LOCK_XACT LCX_NULL 0000:00001073 NULL NULL
00000081:000001b0:0003 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0004 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0005 LOP_SET_BITS LCX_SGAM 0000:00000000 Unknown Alloc Unit NULL
00000081:000001b0:0006 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0007 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0008 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0009 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000a LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000b LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000c LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000d LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000e LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:000f LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0010 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0011 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0012 LOP_MODIFY_ROW LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0013 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0014 LOP_SET_BITS LCX_SGAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0015 LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0016 LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0017 LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0018 LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:0019 LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001a LOP_MODIFY_ROW LCX_PFS 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001b LOP_SET_BITS LCX_GAM 0000:00001073 Unknown Alloc Unit NULL
00000081:000001b0:001c LOP_SET_BITS LCX_IAM 0000:00001073 Unknown Alloc Unit NULL
etc
The transaction with ID 0000:00001072 is the implicit transaction of my TRUNCATE TABLE statement (as you can see from the transaction name). It commits at LSN 00000081:000001aa:0018 and then straight afterwards is the start of the deferred-drop transaction. As you can see from the log records, it's just deallocating the pages and extents.
Well, you can't really see that unless you know what all the log records are doing, so let's have a quick look at the descriptions:
SELECT
[Current LSN], [Operation], [Lock Information], [Description]
FROM fn_dblog (NULL, NULL);
GO
and you'll be able to see the locks that are logged to allow fast recovery to work (see my blog post Lock logging and fast recovery for an in-depth explanation) and also the description of the operations being performed. Here's a small selection from the start of the deferred-drop transaction:
Operation Lock Information Description
————– ———————————————————– ——————————————————–
LOP_BEGIN_XACT NULL DeferredAllocUnitDrop::Process
LOP_LOCK_XACT HoBt 0:ACQUIRE_LOCK_IX ALLOCATION_UNIT: 8:72057594042384384
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:0
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:152 Deallocated 0001:00000098
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:1
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:156 Deallocated 0001:0000009c
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:2
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:157 Deallocated 0001:0000009d
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:3
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:158 Deallocated 0001:0000009e
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:4
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:159 Deallocated 0001:0000009f
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:5
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:160 Deallocated 0001:000000a0
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:6
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:161 Deallocated 0001:000000a1
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X RID: 8:1:153:7
LOP_MODIFY_ROW HoBt 72057594042384384:ACQUIRE_LOCK_X PAGE: 8:1:162 Deallocated 0001:000000a2
LOP_SET_BITS NULL ClearBit 0001:000000a0
LOP_SET_BITS NULL Deallocated 1 extent(s) starting at page 0001:000000a0
LOP_SET_BITS NULL
LOP_MODIFY_ROW Deallocated 0001:000000a8;Deallocated 0001:000000a9;Deallocated 0001:000000aa;Deallocated 0001:000000ab;Deallocated 0001:000000ac;Deallocated 0001:000000ad;Deallocated 0001:000000ae;Deallocated 0001:000000af
LOP_SET_BITS NULL Deallocated 1 extent(s) starting at page 0001:000000a8
LOP_SET_BITS NULL
LOP_MODIFY_ROW Deallocated 0001:000000b0;Deallocated 0001:000000b1;Deallocated 0001:000000b2;Deallocated 0001:000000b3;Deallocated 0001:000000b4;Deallocated 0001:000000b5;Deallocated 0001:000000b6;Deallocated 0001:000000b7
The first 8 operations are deallocating the 8 pages that are allocated from mixed extents when the table was first populated and after that it switches to deallocating an entire extent at a time. Have a poke around – this stuff's really fascinating. Note also the LOP_LOCK_XACT log record, which just describes the acquisition of a lock – not a change to the database. You'll notice that the extent deallocations don't have any locks protecting them – that's what the allocation unit IX lock is doing.
By the way, if you have nonclustered indexes on the table too, they are also dealt with the same way and there will be a single deferred-drop transaction which deallocates all the pages from both the table and all nonclustered indexes, one allocation unit at a time. Try it and you'll see what I mean.
Myth debunked!
PS There's another myth that a TRUNCATE TABLE can't be rolled back – I debunk that in this old blog post: Search Engine Q&A #10: When are pages from a truncated table reused?
10 thoughts on “A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged”
Well, that was edifying. Thanks for sharing.
This one is definitely a well set myth. I remember learning from a certified instructor (back w/ with 7.0 or 2000) that TRUNCATE TABLE was a non-logged procedure. Looking back at the older SQL Server 2000 documentation certainly suggests it is non-logged ("Because TRUNCATE TABLE is not logged, …")
The current documentation certainly makes it clear (even the 2005 doc set clarifies this).
I just tried it out – BEGIN TRANS / ROLLBACK. Works like a charm. Previously I had been told that one couldn’t ROLLBACK (which is why it was faster). All these years writing complicated DELETE scripts and I could have used TRUNCATE.
Now I know different!
Thanks.
Gorgeous !
Interesting. What I thought was "No logging" is actually "Minimal Logging".
http://msdn.microsoft.com/en-us/library/ms191244.aspx
Hi Paul – I have read on BOL that truncate takes a sch-m lock but it doesn’t say much after that. Do you know what part of truncate issues this? You state no locks need to be acquired while deallocating entire extents, so I am wondering where the sch-m comes into this?
thanks.
The SCH_M lock is when it’s moving the allocation units to the deferred drop queue.
Thank you.
Recovery model of a database is full and i execute truncate command on a table.
After truncate table i insert new value to table and DBCC page command shows : the value of the page has been changed.
I taking log backup from the database and restoring to point of time before truncate.
After restore all values truncated exist at table!
SQL server how does it ?
https://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth
Therefore, after truncate operation, records logs are written asynchronous in log file.
Thank you.