SQL Server misunderstanding: About the 19th-day Truncate table operations will not be recorded in logs

Misunderstanding #19: operations on the Truncate table will not be logged

Error



Operations in the User table are recorded in logs. The only operation in SQL Server that is not logged is row Version Control in TempDB.

The Truncate Table statement deletes all data in the entire Table. However, the deletion method is not to delete a row, but to release the data page of the table, the process of releasing pages related to the table group to a background thread for queue processing is called deferred-drop. The advantage of using background threads to process deferred-drop is that this operation does not take the transaction to be executed for a long time, so a large number of locks are not required. In versions earlier than SQL Server 2000SP3 (this version introduces deferred-drop), too many locks run out of memory when Truncate Table.

The following is the test code:
Copy codeThe Code is 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 1, 1280

CHECKPOINT;
GO


The above test database recovery mode is simple, so every Checkpoint will cut off the log (just for simplicity, haha ).

One minute later, let's see how many records are in the log.

Copy codeThe Code is as follows:
Select count (*) FROM fn_dblog (NULL, NULL );
GO



The number of log entries is 2.

If the number you get is not 2, perform another Checkpoint until the data is 2.

Now that the existing logs are known, the log growth is caused by subsequent operations. Run the following code:
Copy codeThe Code is as follows:
Truncate table t1;
GO

Select count (*) FROM fn_dblog (NULL, NULL );
GO


We can see that there are now 541 logs. Obviously, the Truncate operation needs to be recorded in the log. However, it can be seen that Truncate does not delete data row by row, because the 541 log records Delete 1280 data records.

Run the following statement to view logs:
Copy codeThe Code is as follows:
SELECT
[Current LSN], [Operation], [Context],
[Transaction ID], [AllocUnitName], [Transaction Name]
FROM fn_dblog (NULL, NULL );

The result is as follows:

Figure 1. view the Truncate log (partial)

From the log, we can see that the first explicit start of the Truncate Table transaction and the last one starts DeferredAlloc. As you can see, the Truncate operation only releases the pages and partitions that make up the table.

The following code shows the detailed operation description of the log:
Copy codeThe Code is as follows:
SELECT
[Current LSN], [Operation], [Lock Information], [Description]
FROM fn_dblog (NULL, NULL );
GO


Result 2:

Figure 2. Log operation description (excerpt)

You can see the related locks added for the purpose of rapid recovery (you can learn more in my blog post: Lock logging and fast recovery ).

As shown in the above log, this operation will add relevant locks to the eight pages, and then the entire zone will be released at one time. After the release, the related zone will be added with an IX lock, that is, it cannot be used again. After the transaction is committed, the deferred-drop operation will be performed. Therefore, the Truncate table operation can be rolled back.

In addition, if the table has non-clustered indexes, the operation method is similar. It is to hand over to a background thread and then release the table and index pages. The minimum unit for release is each allocation unit. Follow the steps above to try it and you will be able to understand what I mean.

PS: there is also A misunderstanding that the Truncate Table operation cannot be rolled back. I am at: Search Engine Q & a #10: When are pages from A truncated table reused? This article provides a detailed explanation.