How does SQL Server reuse reserved pages after deleting a table

I just want to know:

• How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1)

• How SQL Server reuses reserved space after truncating a table? (call this table T2)

Both are clustered tables (not heaps).

Quite simply a DROP or TRUNCATE statement marks any allocated pages that were associated with the table (regardless if it was a heap or a clustered table) as no longer being allocated and the space is reused by the next operation that requires additional space (e.g. new records, a page split, etc.).

From sys.allocation_units (Transact-SQL):

ⓘ Note

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

A TRUNCATE TABLE operation is effectively the same thing as a DROP TABLE statement, but it comes with the bonus of recreating the table immediately after it has been dropped. This is why a TRUNCATE TABLE statement is a Data Definition Language (DDL) statement and not a Data Manipulation Language statement such as a DELETE.

If you want to look at how the engine deallocates and reuses space, you can dig into the DBCC PAGE undocumented function. Paul Randal has a few articles (e.g. ref1, ref2, ref3, ref4) about its use that you can read through if you're so inclined.

If you've got a sandbox environment, you can also run the following series of scripts which will show the pages being allocated and deallocated in the same manner, regardless if a TRUNCATE TABLE or DROP TABLE operation is performed:

```
-- Create Test Database
USE [master]
GO

EXECUTE AS LOGIN = 'sa'

CREATE DATABASE [TestDB]

REVERT
GO

USE [TestDB]
GO

-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
--Only 1016 pages are reserved. This is because that's the initial size needed by data populating a "blank" database

ParentObject Object Field VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header GAM: Extent Alloc Status (1:0) - (1:312) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:320) - (1:1016) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
The SGAM also shows only 1016 pages are reserved, this view will remain relatively unchanged throughout the demo

ParentObject Object Field VALUE
------------- -------------------------- ------------------------ --------------
SGAM: Header SGAM: Extent Alloc Status (1:0) - (1:304) NOT ALLOCATED
SGAM: Header SGAM: Extent Alloc Status (1:312) - ALLOCATED
SGAM: Header SGAM: Extent Alloc Status (1:320) - (1:1016) NOT ALLOCATED
*/

-- Create our Test Table
CREATE TABLE T1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Value CHAR(8000)
)
GO

-- Populate it with exactly 1GB of data
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
CROSS JOIN sys.configurations t2
CROSS JOIN sys.configurations t3
CROSS JOIN sys.configurations t4

-- Table is 1GB in Size, as expected
EXEC sp_spaceused 'T1'

/*
name rows reserved data index_size unused
----- ------- ----------- ----------- ----------- -------
T1 131072 1052616 KB 1048576 KB 3904 KB 136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile

ParentObject Object Field VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header GAM: Extent Alloc Status (1:0) - (1:8080) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:8088) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:8096) - (1:16168) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:16176) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:16184) - (1:24256) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:24264) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:24272) - (1:32344) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:32352) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:32360) - (1:40432) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:40440) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:40448) - (1:48520) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:48528) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:48536) - (1:56608) ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:56616) - NOT ALLOCATED
GAM: Header GAM: Extent Alloc Status (1:56

StackExchange Database Administrators Q#187552, answer score: 6