SQL SERVER – What Are Ghost Records and How to Clean Them Up?

Ghost Records are logically deleted index records waiting for physical cleanup. I investigate their context before recommending an intervention.

A participant asked this question during my performance-tuning workshop. The distinction between a logical deletion and later physical cleanup was the starting point. It doesn’t make every cleanup command appropriate for routine use.

-- Select the intended database first and replace this schema-qualified table.
DECLARE @ObjectID int = OBJECT_ID(N'dbo.TestTable', N'U');
IF @ObjectID IS NULL THROW 50000, 'The specified table was not found.', 1;
SELECT DB_NAME(database_id) AS DatabaseName,
       OBJECT_SCHEMA_NAME(object_id, database_id) AS SchemaName,
       OBJECT_NAME(object_id, database_id) AS TableName,
       index_id, partition_number, ghost_record_count, version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED');

Run the scoped diagnostic in the intended database. If OBJECT_ID returns NULL, passing it directly to the function would broaden the scan; this guard prevents that. DETAILED can read many pages and should be planned for the table size and workload. Results are per index, partition and allocation-unit context, not one universal database total.

The background ghost-cleanup task normally removes eligible records. Row versions, active transactions and workload conditions can delay reclamation. Inserts or updates don’t automatically make every record a ghost.

I previously recommended sp_clean_db_free_space before backups. That procedure scrubs residual information across data pages and can generate substantial I/O. It isn’t routine backup preparation or a lightweight ghost-clearing command. Use it for its documented purpose with an operational plan.

Reference: Residual-data cleanup scope.

Related reading

• SQL Server Performance Tuning Practical Workshop

Ghost cleanup is not a backup prerequisite, it is background reclamation whose eligibility depends on the workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.