What Are Ghost Records?

Have you ever had to deal with ghost records? While I’ve never had a problem that required intervention, I’ve read about people who have.

Let’s talk about what ghost records are and how you should treat them if they’re giving you problems in your environment.

Where Do Ghost Records Come From?

When a record in SQL Server is deleted, it does not completely vanish all at once. The deleted record is marked as ready to be deleted and still continues to physically exist on disk. These deleted records in a state between active and deleted are considered ghost records. Eventually, a background process in SQL Server will run to physically clean up these ghost records.

To determine how many ghost records you have across your databases, you can run this query:

SELECT SUM(ghost_record_count) Total_Ghost_Records, db_name(database_id) AS 'Database'
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED')
GROUP BY database_id
ORDER BY total_ghost_records DESC;
GO

What Issues Can Ghost Records Cause?

If your environment doesn’t have many deletions to account for, you’ll probably be just fine letting ghost records clean up on their own. If you are running a busy server with a lot of deletes to manage, the ghost cleanup process might do more harm than good by “keeping pages in the buffer pool and generating IO.” If this is the case, you can consider enabling trace flag 661, which will disable the ghost cleanup process.

Even then, this isn’t a situation where you can just look the other way and pretend everything is peachy. Those deletes will fill up space without the ghost cleanup process to manage them. One way to handle this is to manually run the ghost cleanup process when it is best for you. This can be done for all database files by running sp_clean_db_free_space or for one database file at a time by using sp_clean_db_file_free_space.

Running for all database files:

USE master;
GO
EXEC sp_clean_db_free_space @dbname = N'Callihan_Test'

Running for a specific database file:

USE master;  
GO  
EXEC sp_clean_db_file_free_space @dbname = N'Callihan_Test', @fileid = 1;

Don’t Cross the Streams

If you didn’t know what ghost records were before, now you know what they are, how to find them, and how to address them if you have too many.

Thanks for reading!