July 3, 2015 at 4:27 am
deleted 200 million records from table using job. Job completed successfully but Ghost Cleanup is holding the process,its been almost 7hours the process is holding the table. Is this normal??
July 3, 2015 at 5:33 am
Hi Gregg,
hmm, that is rather awkward - 7 hours? That does seem rather long...
Remember that the ghost cleanup is a single threaded process and it can actually be in a position of permanent "lag" behind SQL deletes where it can never catch up.
200 million is a somewhat arbitrary number as it does not reflect the volume of real data (ie the width of the rows in terms of data types) and the underlying speed of the hard drives servicing this request.
If you are not getting a resolution by waiting, you can try to force a clean up of removed records by doing an index rebuild on the table - and other tables that may have a high volume of deletes happening.
There is also the possibility of actually using trace flag 661 to disable the ghost cleanup process - then you can try a rebuild of the table and switch it back on. (Be careful, this can lead to a growth in the SQL files.)
Good luck and let me know how it goes.
July 3, 2015 at 6:56 am
Thanks Zksod.
it got completed. will try steps you have mentioned when get a chance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply