patternsqlMinor
Remove version ghost records of allocation type LOB_DATA without restarting the service or failing over
Viewed 0 times
withoutthefailingversionrecordsremovetypelob_dataserviceghost
Problem
A database that is part of a SQL Server Always Availability Group with both synchronous and asynchronous readable secondaries that are being used to scale out read-only workloads is experiencing a build up of version ghost records of allocation type
This occurs on a table that has a high level of
Performing a manual fail-over removes the version ghost rows, but this is undesirable.
While I am investigating the root cause of the build up of version ghost rows, is there any other way of removing version ghost records of allocation type
LOB_DATA.This occurs on a table that has a high level of
INSERT operations applied to it.REBUILDing the clustered index of this table removes any version ghost records of allocation type IN_ROW_DATA but not of allocation type LOB_DATA.Performing a manual fail-over removes the version ghost rows, but this is undesirable.
While I am investigating the root cause of the build up of version ghost rows, is there any other way of removing version ghost records of allocation type
LOB_DATA?Solution
Check that ghost cleanup isn't disabled with trace flag 661.
A query that reads all the LOB data should clean up ghosts if it can.
Ensure you are on the latest build of whichever version of SQL Server you are using. Ghost cleanup bugs are rare these days, but they were prevalent in the past.
You could also try
If none of those things help, you may have encountered a new issue, which ought to be reported to Microsoft Support.
A query that reads all the LOB data should clean up ghosts if it can.
Ensure you are on the latest build of whichever version of SQL Server you are using. Ghost cleanup bugs are rare these days, but they were prevalent in the past.
You could also try
sp_clean_db_free_space or sp_clean_db_file_free_space, depending on how your data is arranged.If none of those things help, you may have encountered a new issue, which ought to be reported to Microsoft Support.
Context
StackExchange Database Administrators Q#299506, answer score: 6
Revisions (0)
No revisions yet.