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

What are Ghost Records and How to Clean Them Up?

I loved this question when I received it last week in my SQL Server Performance Tuning Practical Workshop.

Well, in simple possible words – Ghost records are those records which are deleted logically from the table but physically still exists on the disk.

For example, if there is a large amount of insert, update or delete happens on your database. SQL Server indeed reflects the changes when you select the data. However, it does not delete the record physically. These records are called Ghost Records.

Here is the script which you can use to identify the ghost record count.

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'SQLAuthority'), OBJECT_ID(N'TestTable'), NULL, NULL , 'DETAILED')
GO

Here is another script which you can use to clear all the ghost records from your database.

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

I see lots of people executing above script before they take backup. Honestly, I think it is alright to take backup without executing above SP as SQL Server frequently clears up all the ghost records. However, with that said, if you feel comfortable running above SP before taking a backup, I think it is totally fine too. Just an extra few minutes before the backup would not hurt you if your backup is finished in your maintenance window.

Reference: Pinal Dave (https://blog.sqlauthority.com)