reaching out for any guidance for a problem I am seeing currently, one of my SQL servers is not releasing space after rows are deleted.
I am running SQL Server 2008 Enterprise x64 with SP1 CU4 (10.0.2734) and have a four node peer-peer replication configuration.
I am seeing large numbers records ready to be consumed by the ghost cleanup task via sys.dm_db_index_physical_stats (ghost_record_count), however these counts do not decrease.
Strangely I am only experiencing this issue on one of the servers in the four node peer-peer configuration, that is the other three nodes successfully cleanup deleted rows with their respective ghost_record_counts eventually being reduced to zero. The node that is experiencing this issue was the principal/initial node used to restore/setup the other nodes in the peer-peer replication topology.
I have tried both reorganizing and rebuilding the effected indexes, however this does not change the ghost_record_count figures after completion. A full CHECKDB also does not yield any error/consistency issues.
Help? :)