The previous post examined the complete delete process and demonstrated the distinction between the logical deletion of a row and its eventual physical removal. It also explored how ghost records are marked and how each stage is recorded in the transaction log.
In this post, we’ll investigate a particularly unusual case of index corruption where the evidence appeared to point towards the ghost cleanup process itself.
If you haven’t read the earlier posts, it’s worth starting with them before continuing. They introduce the ghost record lifecycle, the cleanup process, and the transaction log behaviour needed to follow this investigation.
- Post 1 – Ghosts! – Ghost Records and the Ghost Cleanup Process
- Post 2 – Seeing the ghost cleanup process in action
- Post 3 – Let the ghosts linger
- Post 4 – Ghosting in the transaction log
- Post 5 – The ghostly case of index corruption
- Post 6 – Questions answered
A Ghostly Discovery
While supporting a client system, we received repeated alerts reporting corruption in specific non-clustered indexes.
Although the corruption initially affected only one or two non-clustered indexes, it eventually spread to every non-clustered index on the affected table.
The issue occurred a couple of years ago now, on a system running SQL Server 2016 SP2, and the behaviour appeared to be an exceptionally rare edge case. We’ve not seen another since.
Investigations
Our investigations established that the issue consistently appeared after index maintenance, specifically when the affected indexes were reorganised and subsequently checked for consistency. The maintenance operation itself completed successfully and did not raise any errors. Corruption was only identified afterwards, either through error messages recorded in the logs or when DBCC CHECKDB reported inconsistencies. Since CHECKDB detects corruption rather than causes it, it was ruled out as the source. Even so, the sequence of events was unusual enough to make this one of the more mysterious investigations we had encountered.
Who you gonna call?
Further investigation, including escalation to Microsoft, ruled out Azure storage and any known issues in the SQL Server product. Relevant logs and diagnostics were shared, and Microsoft conducted a thorough investigation into the issue.
Further investigations and issue elimination testing
Whilst Microsoft continued reviewing logs and investigating the SQL Server code base, we focused on supporting the client. Keeping the service available was the priority, and our efforts centred on maintaining stability whilst the investigation continued.
Reducing the opportunity for corruption
Our initial investigations proved correct, and we began work to reduce the amount of index maintenance required, thereby minimising the likelihood of this type of corruption occurring.
The first step was to lower the fill factor on the table’s indexes. By reducing the rate at which fragmentation accumulated, the frequency of index maintenance operations could also be reduced.
This had the desired effect, but as predicted, it wouldn’t resolve the issue forever and we were already working on the next phase…
Automatically fixing corruption of non-clustered indexes
Non-clustered indexes can be repaired either by rebuilding them or by dropping and recreating them, because their contents are generated from the underlying table data. Knowing this, we implemented a series of automated jobs to detect corruption and repair the affected non-clustered indexes whenever it occurred.
Limitations
Our client operated a 24×7 system, so opportunities to rebuild indexes were limited. Their preference was to work with slightly stale data and restrict maintenance to approved windows, despite the possibility of different indexes returning different results. In this scenario, service availability and rapid data retrieval were considered more important than absolute data freshness.
Mitigation Steps
Index maintenance was reconfigured for the affected table. Index reorganisations were eliminated and replaced with rebuild operations triggered at a lower fragmentation threshold, using a drop-and-recreate approach.
We also configured a detection, alert, and fix solution:
SQL Alert
A set of SQL Server Alerts were created to detect corruption by monitoring for error 5257 and the associated index name in the error log. Each index on the table had its own alert configured, ensuring that corruption could be detected and addressed independently.
The alert raised the alarm in monitoring and triggered the Enable Job…
Enable Job
As the job to fix the corruption could not run until a specific time, and wasn’t needed all the time, the jobs to fix by way of a ‘Rebuild’ of indexes were disabled. When an alert fired, it enabled the required rebuild job so it could run during the next approved maintenance window.
Rebuild Job
Once enabled, the rebuild job could run during the client’s defined maintenance window. The jobs were made up of several steps:
- Disable
- Disable the job so it wouldn’t run again
- Rebuild Index
- Verify the index exists and drop it if present
- Rebuild the index using the required fill factor and settings
- Retry automatically if a deadlock occurred
- Alert Update
- Send an update email on completion
Index Optimise
A new index optimise job was created that, when required, ran the above rebuild step. The fragmentation levels were checked, and dynamic SQL was generated to start the corresponding rebuild job for each affected index on the table.
What about the Ghost Busters?
After Microsoft investigated the issue, they concluded that it was potentially an edge case involving the ghost cleanup process. They advised that a fix for SQL Server 2016 SP2 was unlikely because the version was already outside mainstream support at that time. Microsoft suggested disabling the ghost cleanup process to determine whether corruption would continue to occur and thereby confirm the theory.
We had already mitigated the recurring issue and reduced the known risks to a level that was understood and accepted by the business. Introducing risk by disabling the ghost cleanup process was not considered acceptable and, as a result, this interesting case remains technically unproven.
Why point at the ghost records
Throughout the series of posts, we have demonstrated what happens when a record is deleted: it is marked as a ghost record and later cleaned up.
If the ghost cleanup process were to incorrectly process a ghost record, the internal indicators used to track ghosted rows could become inconsistent. If the metadata associated with a ghost record were updated without the corresponding physical row being removed, SQL Server could interpret the resulting mismatch between metadata and underlying data as index corruption.
Without the opportunity to reproduce the issue under controlled conditions, this remains supernatural speculation rather than a proven root cause.
Closing Thought
It is important to note that this theory was never conclusively proven. Microsoft identified the ghost cleanup process as the most likely area of concern and suggested disabling it as a test. However, given that an effective mitigation had already been implemented and accepted by the business, introducing additional risk into a production system was not considered appropriate.
Consequently, this remains one of the most unusual SQL Server corruption investigations we’ve encountered: a case where the evidence appeared to point towards the ghost cleanup process, but a definitive root cause was never established.
Questions?
This marks the end of the main series, but we’d love to hear your thoughts. If there’s anything you’d like us to explore further, or if you have your own experiences with ghost records, ghost cleanup, or related SQL Server internals, please get in touch. We’ll publish a follow-up Q&A covering the questions, feedback, and interesting observations raised throughout the series.
Thank you for following along. We hope the series has provided some useful insight into ghost records, ghost cleanup, and the SQL Server internals that work behind the scenes.