none
Ghost Cleanup Task not removing deleted rows, SQL2008 SP1 CU4 RRS feed

  • Question

  • Hi, 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? :)

    Tuesday, August 10, 2010 7:50 AM

All replies

  • Ouch.

    Well, a few questions.  Is there a clustered key? If so, that's the one to try to reorg and rebuild.  If not, I might ask, why not?

    Does your database have just the primary filegroup, or are the others, and are the others involved in this table?  Do you use either of the snapshot isolation levels? How large is the table, in rows and megabytes?

    Saw the ghost record bugs in 2005, hadn't seen any yet in 2008.

    Josh

     

    Tuesday, August 10, 2010 1:59 PM
  • Hi JRStern, thanks for your time.  I have deturmined that the issue is not isolated to a particular table or database on this server, but is effecting all databases on this server (but does not effect the other servers in the four node peer-peer configuration).

    Yes the table has a clustered key (the only index on the table), have included the table schema below (table/db names changed). 

    The database has three filegroups, this table stored on the second filegroup, have included the database schema below.

    Snapshot isolation is off, read committed snapshot is on.  The table is quite large, 5,773,878 rows, 125GB, which is what prompted an archiving process to delete rows.

    Interestingly having restored a backup of this database to another server (same SQL version/patch level) the ghost cleanup task eventually clears the deleted records and the ghost_record_count reaches zero by the next day.  However as on the orignial server, an index rebuild or reorg does not clear the ghost_record_count for the table.

    CREATE DATABASE [MyDatabase] ON PRIMARY

     

    (

    NAME = N'MyDatabase_FILE1', FILENAME = N'G:\PrimaryData\MyDatabase_FILE1.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ),

    FILEGROUP [MyDatabase_FILEGROUP2] DEFAULT

    (

    NAME = N'MyDatabase_FILE2', FILENAME = N'G:\PrimaryData\MyDatabase_FILE2.ndf' , SIZE = 286261248KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),

    FILEGROUP [MyDatabase_INDEX_FILEGROUP1]

    (

    NAME = N'MyDatabase_INDEX_FILE1', FILENAME = N'H:\IndexData\MyDatabase_INDEX_FILE1.ndf' , SIZE = 8388608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 524288KB )

    LOG ON

    (

    NAME = N'MyDatabase_log', FILENAME = N'I:\Log\MyDatabase_log.ldf' , SIZE = 15204352KB , MAXSIZE = 2048GB , FILEGROWTH = 524288KB )

    GO

     

    CREATE

    TABLE [dbo].[MyTable](

    [RowID] [bigint] NOT NULL,

    [Data] [varchar](max) NOT NULL,

    CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED

    (

    [RowID] ASC

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyDatabase_FILEGROUP2]

    )

    ON [MyDatabase_FILEGROUP2]

     

    Results of sys.dm_db_index_physical_stats for this table, note the ghost_record_count for the LOB_DATA row;

    ObjectName database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count compressed_page_count
    MyTable 17 325576198 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 1.988052 11620 7.029948 81688 93.85016 5814900 0 0 83 7151 104.886 NULL 0
    MyTable 17 325576198 1 1 CLUSTERED INDEX LOB_DATA 1 0 0 NULL NULL 16306000 54.59554 13978300 12061700 0 523 8068 5140.482 NULL NULL

    Wednesday, August 11, 2010 1:59 AM
  • Well that's something, that it's the LOB_DATA, but I'm not sure just what!

    It does explain why the reorg does not kill the ghosts - the only way to physically move the LOB data is to copy the whole contents to another table, AFAIK, better yet if the other table is in another filegroup.  We had to fight this in SQL 2005, I forget the details, and 2008 might behave differently anyway.

    Is it possible that your main machine is so busy that it just never calls the ghost collector?

    It certainly constitutes enough of a bug, that I hope you pursue it with Microsoft ... as frustrating as I'm sure that can be.

    Josh

    ObjectName

    index type desc

    alloc unit type desc

     record count

     ghost record count

     version ghost record count

    database id

    object id

    index id

    partition number

    index depth

    index level

    avg fragmentation in percent

    fragment count

    avg fragment size in pages

     page count

    avg page space used in percent

     min record size in bytes

     max record size in bytes

     avg record size in bytes

    forwarded record count

    compressed page count

    MyTable

    CLUSTERED INDEX

    IN_ROW_DATA

           5,814,900

                     -  

                   -  

    17

    325576198

    1

    1

    3

    0

    1.99

    11620

    7.03

            81,688.00

    93.85

                  83

             7,151

                105

    NULL

    0

    MyTable

    CLUSTERED INDEX

    LOB_DATA

         13,978,300

      12,061,700

                   -  

    17

    325576198

    1

    1

    1

    0

    0.00

    NULL

    NULL

     16,306,000.00

    54.60

                523

             8,068

             5,140

    NULL

    NULL

    Wednesday, August 11, 2010 3:18 AM
  • Have rebooted the offending server and the issue has resolved itself - the ghost-cleanup task has cleared the ghost_record_count rows by the next day :)  Database free space has increased dramatically.

    I wouldn't say that the issue resolving itself after a reboot does much for my confidence as I do not know what lead to the problem, will need to keep an eye on the ghost_record_count's in my daily health-checks.

    Thursday, August 12, 2010 12:33 AM
  • This bug exists in both SQL 2008 and SQL 2008 R2. Microsoft now has a fix. http://support.microsoft.com/kb/2622823

    Tuesday, May 22, 2012 7:13 PM