Do ghosts really exist in SQL Server?

In this article I am trying to cover what are Ghost Records and what role they play in SQL Server.

• Ghost records are those that have been logically deleted but not physically deleted from the leaf level of an index (also a heap).
• The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits.
• Once this is done, it is deleted by an asynchronous background process (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.
• Ghost Record Cleanup improves the performance of the DELETE command because SQL Server doesn't have to deal with the physical cleanup right away.

How to identify if Ghost records exist on your databases?

For SQL Server 2000/ SQL Server 2005/ SQL Server 2008












Output will be as below:

For SQL Server 2005/SQL Server 2008



















Interesting points:

Consider a scenario. I have disabled the Ghost-Cleanup task for the server. I have a database DB_Test with only 1 table name Tbl_Table with 1 million records occupying 10GB of disk space. I decide to delete all the contents of the table. I fire the delete statement and it completes successfully. I now decide to shrink the database, you would observe that the file will not be successfully shrunk. The reason is that the ghost-cleanup task has not physically cleaned up the records.
FYI, to disable the ghost-cleanup task the trace flag to be enabled is : 661. Hence, in the future, if you face a scenario when the shrink operation is not succeeding in shrinking the file size though the parameters look correct, you might want to check the presence of gust records and that the trace flag 661 is not enabled.