April 27, 2017 at 3:49 am
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 half an hour whilst writing this post and has yet to return.
I've disabled ghost cleanup temporarily at least by switch on flag 661 - dbcc tracestatus reports status as 1 (global is also 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.
April 28, 2017 at 12:30 am
Update for posterity:
Started sql in single user mode, no background processes. Was able to run a checkdb that reported over 45,000 consistency errors.
No idea what causes the errors but resolved by restoring a backup, as a precaution the DB was restored to different drives.
June 29, 2017 at 11:24 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply