2

Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing.

I was checking the "Free Space" parameter in the Database and always was at the same value (Around 1GB).

I did a backup of the database and restored in the same server with the same name to see if running sp_clean_db_free_space would free up the space that GHOST_CLEANUP was not able to. After 2 hours...TADA!...the space was cleaned and I had 80GB free from 100GB Database.

So I decided to run sp_clean_db_free_space stored procedure on the original database to see what happens. After almost 30m it finished not freeing any space. The only different thing was that the original database was synched under an Availability Group. I disconnected the database from the Availability Group and now I can see that the GHOST_CLEANUP task is slowly freeing space.

I will assume that the problem was the Availability Group, but does anyone know WHY!?

I can confirm no blocking on primary by read operations on secondary because the secondary node is not even used for read only operations as the application doesn't allow to configure this. There are no users connected to the secondary node.

Today, after checking I found that each 5 seconds SQL Server jumped from 0-1% CPU to 10-15% CPU then dropped again to 0-1% CPU after 1 second. No I/0 usage, only CPU. Decided to disconnect all my Availability Replicas, after 1 minute, SQL Server dropped to a stable 0-1% CPU, connected the replicas again and remains at 0-1%. I have a problem with this GHOST_CLEANUP and I don't know how to solve it.

I am using Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64).

  • It could also be a SQL agent task, or incomplete redo (there's no mention of whether you were running in sync or async commit). Did you capture information on every spid that was running on the secondary? What was the value in dm_hadr_database_replica_states for the low_water_mark_for_ghosts vs the primary replica? – Nic Oct 3 '16 at 19:33
-3

There is a connect item. It is fixed in SP4.

  • 2
    Can you provide a link to the connect item in your answer? Thank you. – Joe Obbish Aug 7 '17 at 16:01

Your Answer

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

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