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.
- Inside Storage Engine Ghost Cleanup in Depth
- 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.
