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?”
Great post, Paul!
Clear and understandable, as always, keep blogging! :)
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.
Because that would require scanning all the rows, which defeats the purpose of the fast truncate. 2) because delete is a row-by-row operation, not a bulk delete.