Myth #19: TRUNCATE TABLE operations are not logged to the log
Error
Actions in the user table are recorded in the log. The only operation in SQL Server that is not logged to the log is row versioning in tempdb.
The Truncate table statement deletes all data from the entire table. But the way to delete is not to delete a row, but to release the data page that makes up the table, the process of releasing the related pages that make up the table to a background thread for queue processing is called Deferred-drop. The advantage of using background threading to handle deferred-drop is that this operation does not cause the transaction to take a long time to execute, and therefore does not require a large number of locks. In the previous version of SQL Server 2000SP3, which introduced Deferred-drop, it was commonplace to have too much lock-running memory at TRUNCATE table.
Here is the test code:
Copy Code code as follows:
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);
Go
SET NOCOUNT on;
Go
INSERT into T1 DEFAULT VALUES;
Go 1280
CHECKPOINT;
Go
The test database recovery model above is simple, so each checkpoint will truncate the log (just for simplicity, haha).
In a minute, let's see how many records there are in the log.
Copy Code code as follows:
SELECT COUNT (*) from fn_dblog (null, NULL);
Go
As you can see, the log entry number is now 2.
If you get a number that is not 2, do it again checkpoint until the data is 2.
Now that the existing log has been known, then the growth of the log is due to the subsequent operation caused. Below we execute the following code:
Copy Code code as follows:
TRUNCATE TABLE T1;
Go
SELECT COUNT (*) from fn_dblog (null, NULL);
Go
You can see that there are now 541 log records. It is obvious that the truncate operation needs to be recorded in the log. However, you can also see that truncate is not deleted line by row because these 541 log records delete 1280 data.
Execute the following statement to view the log:
Copy Code code as follows:
SELECT
[Current LSN], [Operation], [context],
[Transaction ID], [Allocunitname], [Transaction Name]
From fn_dblog (null, NULL);
Here is the result:
Figure 1. View the log after truncate (partial)
Through the log you can see the first explicitly start TRUNCATE TABLE transaction, the last one begins Deferredalloc. As you can see, the truncate operation simply frees up the pages and extents that make up the table.
The following code can look at the description of the log's specific actions:
Copy Code code as follows:
SELECT
[Current LSN], [Operation], [Lock information], [Description]
From fn_dblog (null, NULL);
Go
The results are shown in Figure 2:
Figure 2. Log Operation description (excerpt)
You can see the associated locks for quick recovery purposes (you can learn more in my blog: Lock logging and fast recovery).
From the above log, this operation will be 8 page plus related locks, and then the entire area released at once. After release, the associated area will be added IX lock, that is, can no longer be used, when the transaction is committed before the deferred-drop, so that the TRUNCATE table operation can be rolled back.
In addition, if a nonclustered index exists on the table. Then the operation is similar, is to a background thread and then release the table and index pages. The smallest unit that is released is each allocation unit. Follow the steps above you should be able to understand what I mean by trying it yourself.
PS: There is also a misunderstanding about the TRUNCATE TABLE operation can not be rolled back, I am in: Search Engine q&a #10: When are pages from A truncated table reused? This article is a detailed Explain.
