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).