1

Looking for a little advice. This is all on 2008 R2 SP1.

I have a large table (around half a terabyte) that a ghost cleanup is holding locks on, more specifically exclusive locks on two pages, which I believe are then causing intent exclusive and intent shared on the table. It seemingly won't move away from these pages. The table is in its own filegroup which contains four files. Data is added to the table each day, the only deletes are due to an archiving process. The archive has been run daily since easter, removing a small amount of data each day. What seems to have caused this is one of the files in the filegroup has been moved to another disk using the usual take offline - modify the file details and bringing the db back online.

The cleanup process blocks other operations, including backup. DBCC commands like checkdb / checkalloc etc also get blocked.

I have a query running on sys.dm_db_index_physical_stats to investigate, this appears to be working, albeit slowly, activity monitor shows it having wait type PAGEIOLATCH_SH for a short period (a few milliseconds) for different page numbers on each refresh. This has been running for in excess of 3 hours.

I've attempted to disable ghost cleanup temporarily by switching on trace flag 661 globally - dbcc tracestatus reports status as 1.

Yet the ghost cleanup process persists, even after a restart of the server. sp_who2 shows that the CPU time is increasing, but the DiskIO is not. There is no significant disk access (~2mb/sec).

As I mentioned, the server is running R2 SP1 so the known issue with sql server taking 100% CPU (https://support.microsoft.com/en-us/help/978430/fix-the-ghost-cleanup-task-uses-100-of-the-cpu-on-an-idle-system-in-sql-server-2008-or-in-sql-server-2005) should not apply, in any case sql is only taking a steady 25% CPU.

It could be the issue listed here: https://support.microsoft.com/en-us/help/2622823/fix-ghost-record-count-values-keep-increasing-in-sql-server-2008-r2-or-in-sql-server-2008 but as I am yet to receive a result from the query in the symptoms section I can't confirm.

Is there anything else I can look at to diagnose further ?

Thanks.

  • Did you try DBCC FORCEGHOSTCLEANUP . – SqlWorldWide Apr 27 '17 at 14:17
  • @SqlWorldWide - I did, it completed in 7 seconds, but the stuck process remains.. – Mark Apr 27 '17 at 14:20
  • Ensure you have the most recent updates for SQL Server installed. Check this for details about what service packs and cumulative updates are available. – Max Vernon Mar 19 '18 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.