I have been trying to troubleshoot an issue I have detected on a UAT SQL Server, there is some resource usage even if the server is not being used...

On Activity monitor I have an average of 5% CPU usage (Real SQL Server Usage double checked with DMVs) and also a 20mb/s disk IO, this has been the same for 2 days now...

I have found that the only process that is doing IO Operation is the ghost cleanup...

After reviewing what data file is doing the IO Operation I found that I have 1500Reads/sec (NO WRITE OPERATIONS) on one Database where some users were doing load tests days ago...

What can I do with this, will this process finish? Is there anything I can do?

EDIT

After taking some time on troubleshooting this I finally found that after disconnecting the Database from the Availability Group GHOST_CLEANUP started to really free up space on my Database, took 4 hours for him to free 80GB of Storage. After connecting the Database to the Availability Group GHOST_CLEANUP starts to not free space again... Weird.

up vote 3 down vote accepted

What can I do with this, will this process finish? Is there anything I can do?

This is how Ghost Cleanup works it kicks up after every 10 seconds and does the cleanup. The ghost cleanup does not filters out specific pages to cleanup, when ghost cleanup operation starts it takes up 10 pages which are marked as ghosted and cleans them up. This process happens every 10 sec unless scan which run and finds out ghost records come and says that there is no ghost records.

Yep ghost cleanup is single threaded, I/O intensive and also fills up transaction log and you cannot avoid this unless you disable it. To disable this process you can enabled trace flag TF 661 documented in This support article. As a good advise it is NOT recommended but since you have testing system you can of course try it.

So I believe the testers are doing some big delete operation may be periodically forcing lot of ghost records and this forcing ghost cleanup.

I would suggest you wait till the process completes. Like I said it will pick not more than 10 pages and so depending on amount of pages deleted it may take some time.

Some very good readings on Ghost Cleanup process.

  1. Inside Storage Engine Ghost Cleanup in Depth
  2. Turning Off Ghost Cleanup Tasks for Performance Gains

EDIT:

My main concern however is that if I disable the Ghost Cleanup task, how then I will clean the rows marked to be deleted?

I would like to quote from the post Turning Off Ghost Cleanup

If you disable the ghost cleanup task, the space taken up by deleted records will NOT be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.

One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.

  • Thanks a lot for your advice and your help, definetly will read those articles. Thanks again! – J1mmy Sep 27 '16 at 10:40
  • My main concern however is that if I disable the Ghost Cleanup task, how then I will clean the rows marked to be deleted? I am performing Ola's Index Optimization tasks each 8 hours. – J1mmy Sep 27 '16 at 13:40
  • For me for example, I did a complete Index rebuild on the whole Database, but ghost cleanup is still running even after I did the full rebuild and nobody has done anything on the server :/ – J1mmy Sep 27 '16 at 13:59
  • Yep if you read my comment rebuilding index will not definitely remove all ghost records. – Shanky Sep 27 '16 at 14:17
  • 1
    After taking some time on troubleshooting this I finally found that after disconnecting the Database from the Availability Group GHOST_CLEANUP started to really free up space on my Database, took 4 hours for him to free 80GB of Storage. After connecting the Database to the Availability Group GHOST_CLEANUP starts to not free space again... Weird. – J1mmy Oct 5 '16 at 9:47

Ghost cleanup can sometimes be a problem with availability groups. Look for long running transactions on any of the participating replicas (primary and secondary).

If you can't find any long running transactions and when you’ve exhausted other solutions, call support and/or restart SQL Server when you have an opportunity.

http://michaeljswart.com/2017/08/problem-with-too-many-version_ghost_records/

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.