Search Engine Q&A #10: When are pages from a truncated table reused?

This is a question I was sent a week or so ago – if a table is truncated inside a transaction, what protects the integrity of the table’s pages in case the transaction rolls back? Let’s find out.

First off I’ll create a simple table to experiment with:

CREATE TABLE [TruncateTest] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'A');
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 20)
BEGIN
    INSERT INTO [TruncateTest] DEFAULT VALUES;
    SELECT @a = @a + 1;
END;
GO

We can see what pages and extents are allocated to the table using the undocumented DBCC IND command:

DBCC IND ([test], [TruncateTest], 0);
GO
PageFID PagePID
------- -------
1       193
1       192
1       194
1       195
1       196
1       197
1       198
1       199
1       200
1       224
1       225
1       226
1       227
1       228
1       229
1       230
1       231
1       232
1       233
1       234

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I’ve edited the output to just the page IDs and we can see that there are 4 extents used by this table (starting on pages (1:192), (1:200), (1:224), and (1:232)). Now if we truncate the table in a transaction, what will DBCC IND show?

BEGIN TRAN;
GO

TRUNCATE TABLE [TruncateTest];
GO

DBCC IND ([test], [TruncateTest], 0);
GO
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Looks like there are no pages allocated to the table. So where are they? Let’s check what locks there are. Instead of using sp_lock, I’m going to use it’s replacement DMV, sys.dm_tran_locks:

SELECT
    [resource_type],
    [resource_description],
    [request_mode]
FROM
    sys.dm_tran_locks
WHERE
    [resource_type] IN (N'EXTENT', N'PAGE');
GO
resource_type  resource_description  request_mode
-------------- --------------------- -------------
EXTENT         1:200                 X
PAGE           1:198                 X
PAGE           1:199                 X
PAGE           1:196                 X
PAGE           1:197                 X
PAGE           1:194                 X
PAGE           1:195                 X
PAGE           1:192                 X
PAGE           1:193                 X
EXTENT         1:192                 X
PAGE           1:200                 X
EXTENT         1:232                 X
EXTENT         1:224                 X

Ah – all the pages and extents are locked. The table doesn’t show them as allocated any more but because they’re exclusively locked, the allocation subsystem can’t really deallocate them until the locks are dropped (when the transaction commits). That’s the answer – they can’t be reused until they’re really deallocated. If a transaction rollback happens, the pages are just marked as allocated again.

3 thoughts on “Search Engine Q&A #10: When are pages from a truncated table reused?

  1. The blog is very clear and simple to understand, however i just have one small doubt, what is the thought process behind not allowing WHERE clause while performing TRUNCATE operation, (not possible architectural wise? why can’t DELETE have this Defferred Drop back ground task when performed without WHERE clause) of course who else other than Paul can answer this. Thanks in advance Paul. Sorry my questions is too dumb.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.