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.

Related Posts