Deleting rows from a SQL Server heap can leave empty pages allocated. When a table-level delete is appropriate, DELETE ... WITH (TABLOCK) allows those empty pages to be deallocated; the documented rule does not exclude heaps merely because they contain LOB or row-overflow columns.
Last updated: November 10, 2026.
SELECT index_id, row_count, reserved_page_count, used_page_count,
in_row_data_page_count, lob_used_page_count, row_overflow_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.ImportStage');
DELETE FROM dbo.ImportStage WITH (TABLOCK);
SELECT index_id, row_count, reserved_page_count, used_page_count,
in_row_data_page_count, lob_used_page_count, row_overflow_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.ImportStage');Run the measurement in the same database and confirm that index_id = 0 identifies a heap. Page counts can retain a small allocation after the delete; the important test is whether the large empty allocation was released for reuse.
Apply the documented heap rule
The DELETE documentation says empty heap pages may remain allocated with row or page locking. It lists TABLOCK, TRUNCATE TABLE, or temporarily creating a clustered index as ways to release the pages. It does not state that a varchar(max), xml, or row-overflow-capable column disables this behavior.
Use TRUNCATE TABLE when every row must go and foreign keys, replication, and other restrictions permit it. Use DELETE when row-by-row logging, predicates, triggers, or those restrictions require it. Both choices need a maintenance window appropriate for their locking and log impact.
Check the conditions that really block deallocation
Microsoft’s heap space troubleshooting guidance notes that row-versioning isolation can prevent page release even when a table lock is held. Check snapshot settings, active transactions, and the actual lock acquired before attributing the result to a column type.
Test on the exact supported SQL Server build, because allocation behavior can change. Capture page counts before and after, commit the transaction, and verify reusable database space rather than expecting the physical data file to shrink. Shrinking a file is a separate operation and is not required for other objects to reuse deallocated pages.
