SQL Server: An Examination of Logging in Truncate Table Statement and Its Comparison With Delete Statement

Article
1/17/2024

Introduction

SQL Server books online does not clearly defines whether Truncate statement is minimally logged or fully logged. As per books online TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocation are recorded in the transaction log. Also when compared with delete statement TRUNCATE TABLE is faster and uses fewer system and transaction log resources. In whole definition books online does not mention about how is truncate table logged. SQL Server defines minimally logged operation as 'Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery'. If you go by literal definition of Minimal yes truncate table logs only deallocation of data pages or extents but does not logs information about rows deleted so it does logs information minimally. But if we go by Minimal Logging definition as per Microsoft it does not says anything about logging behavior of truncate statement.

Scope

In this article we will see whether truncate statement is minimally logged or not and will also compare its logging with delete statement. At the end of this article reader would be able to conclude that whether truncate statement is minimally logged or fully logged with limited information.

Note

This article uses fn_dblog and DBCC PAGE command which are undocumented commands. use of such commands were required to prove certain facts related to SQL Server. Undocumented means Microsoft does not support these commands, changes can be made to these commands without giving any information to users so unexpected result might occur. So I advise readers not to use these commands on production database. You can play around with these commands on Test databases.

Creating Scenario For The Test

To perform this test we will use SQL Server 2014 Evaluation edition. One can use SQL Server Standard, Enterprise, Express and Developer editions as well. To get started with lets first create a table and name it as TruncateLogging and insert 100 records in it.

Query 1

--Use DB_name

If exists(select  * from  sys.objects where  name='TruncateLogging' and type='U')
Drop Table  [dbo].[TruncateLogging]
GO

 
CREATE TABLE  dbo.TruncateLogging
(
    id    int       NOT NULL IDENTITY(1, 1),
    col1  char(20) DEFAULT  'A',
  
    CONSTRAINT PK_TruncateLogging_ID PRIMARY KEY  CLUSTERED (id)
);
GO
  
INSERT INTO  dbo.TruncateLogging DEFAULT VALUES;
GO 100

Performing The Test

Performing Test To Show Difference Between Delete and Truncate

Now lets Truncate table TruncateLogging and see how truncate table behaves internally and what gets logged in transaction log. We would use UNDOCUMENTED command fn_dblog to read transaction logs corresponding to truncate table operation. We would run truncate table within a transaction to filter out records corresponding to truncate table.

Query 2

Begin Transaction  Tran1
Truncate Table  [dbo].[TruncateLogging]
 
--Query to look at contents of transaction log
 
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='Tran1'
)
--Rollback Transaction
--Commit Transaction

Below is the output of the query:

Figure 1

From figure 1 above we can see only 11 rows present in transaction log corresponding to truncate table command. If you see rows 3,4,5 they correspond to deallocation of pages 5a and 5d. Pages 5a and 5d are hexadecimal values if we see decimal value of these pages it would be 90 and 93

Lets examine these pages. We would start with page 90. To see the contents of this page we will use DBCC PAGE command and below query to dump output of page in query windows:

Query 3

dbcc traceon (3604)
go
dbcc page (Indexfragmentation,1,90,1)

Below is result of the query:

Figure 2

We can see that m_type=1 which means it is a data page with record type as primary record and we can also see the value 'A' which we inserted into the table. We have entered 20 bytes of char, 4 bytes of integer and additional 7 bytes as page header which makes record size to 31 bytes. Page size is 8 KB which mean 8192 bytes so SQL Server can store 264 such record on a single page and since we inserted just 100 records only a single page would be sufficient to store all data.

Now lest look at page 93:

Figure 3

m_type=10 clearly shows this is an IAM page. IAM page stores information of extents which are allocated to a clustered index or a non clustered index.

Now lets get back to Figure 1

First Lock on IAM page which is page 93 is taken and IAM page was updated with information that page 90 is deallocated and then data page 90 is marked as deallocated from PFS page. . PFS page stores amount of free space on page and this is what updated in PFS page.

Note:

There were 100 rows in page but only information about the page(which stored 100 rows) is logged in transaction log no information about rows present on that page was logged.
Page was not physically deleted its information(that it is been removed) was just updated in metadata, the PFS and IAM pages. This is actually done for performance.

Now lets follow same procedure for DELETE statement by replacing truncate table with delete statement. Before that please restart SQL Server service just to make it simple even if you don't it wont affect. Below is the result and it is interesting:

Figure 4

Delete statement produced 101 rows which is actually information about 100 rows being deleted and one row more which holds information about being transaction. I did not gave commit transaction otherwise it would have had 102 rows. This clearly proves that for delete statement information about each row being deleted is logged and so logs produced will be more.

Its also worth noting that records are directly deleted and the same can be confirmed from LOP_DELETE_ROWS present in the output. Its also worth noting that no update to IAM or PFS page was done in delete operation.

If you note page ID column page ID is always 5a which is page 90 and that is our data page. Also note log record size is constant and = 128
From column Lock Information we can also note that in delete operation whole table was not locked but rows were exclusively(X) locked with Intent exclusive(IX) lock on page where row is present.

Both delete and truncate statement is logged difference is delete is fully logged and in truncate less information is logged.

Performing Test To Show Truncate is Not Minimally Logged

Now its clear that Truncate Statement is not fully logged so now question arises can we say it is minimally logged.To qualify as a minimally logged it must follow to point

Only information to rollback should be present in transaction log for truncate statement no information for roll forward should be present.
Point in time recovery should not be possible
We will take these two points to find out whether Truncate statement is minimally logged or not

To prove first point we will consider the below simple query:

Query 4

INSERT INTO  dbo.TruncateLogging DEFAULT VALUES;
GO 100
 
select count (*) from dbo.TruncateLogging
 
Begin Transaction
  
Truncate Table  [dbo].[TruncateLogging]
  
Rollback

Now if we run the above statement SQL Server will first insert 100 records in table and then in transaction will truncate the table but with rollback as a last statement it will rollback whole truncate statement and if you do count(*) of table again you would see 100 records now since rollback happened so this means enough information was logged to rollback the transaction which proves that truncate statement is not minimally logged.

Checking Log backup to confirm about Bulk logged data

.
SQL Server provides us very powerful RESTORE HEADERONLY command from which you can get lot of good information present in Log backup. Actually it can give very good information for all types of backups but we would concentrate here on log backup.
When you run restore headeronly to get information from log file the output would contain column as HasBulkLoggedData now if the log backup contains bulk logged data this column would have value 1. We can use this command to prove whether truncate statement is minimally logged or fully.

We will perform 2 tests one would be in on database in full recovery other would be on database in bulk logged recovery. We would insert few records in table truncatelogging which is present in database IndexFragmentation( This is DB specific to my testing please change DB name according to your setup)

Query 5

INSERT INTO  dbo.TruncateLogging DEFAULT VALUES;
GO 100
 
Alter database  [IndexFragmentation] set recovery Bulk_logged
go
backup database  [IndexFragmentation] to disk='D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.bak'
go
truncate table  [dbo].[TruncateLogging]
go
backup log [IndexFragmentation] to  disk= 'D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.trn'
go
 
Restore headeronly from  disk='D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.trn'

Below is the output after running restore headeronly command. I have co-leased various irrelevant columns in output

Figure 5

From above figure we can see column HasbulkLoggedData has value 0 which means truncate is not Minimally logged in full recovery.

Now lets do same thing in Bulk Logged recovery model.

Query 6

INSERT INTO  dbo.TruncateLogging DEFAULT VALUES;
GO 100
 
Alter database  [IndexFragmentation] set recovery Bulk_logged
go
backup database  [IndexFragmentation] to disk='D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.bak'
go
truncate table  [dbo].[TruncateLogging]
go
backup log [IndexFragmentation] to  disk= 'D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.trn'
go
 
Restore headeronly from  disk='D:\Truncatelogging\IndexFragmentation_fullbackup_23092014_BL2.trn'

Below is output after running restore headeronly command

Figure 6

In above figure as well we can see HasBulkLoggedData column has value 0.

Above tests prove that Truncate Table statement is not Minimally logged in both Full and Bulk Logged recovery model. As a fact it is never minimally logged in any recovery model considering minimal logging definition as per Microsoft.

Conclusion

Above two demonstrations prove that Truncate Statement does not qualifies to be called as minimally logged operation. The fact that truncate statement can be rolled back and the fact that log backup taken just after truncate statement does not includes bulk logged changes proves that truncate statement is not minimally logged as a fact it would be correct to say Truncate table is logged with less information. Both delete statement and truncate statement are logged but delete statement is more efficiently logged producing more log records.

Share via