Understanding why ghost version records in SQL server 2019 exists across application server restarts?
10
votes
2
answers
1095
views
Please bear with me as I'm not a DBA nor am I a SQL server expert.
We added an index to a table and noticed after about a day or so, the storage for the index grew to around 200 MB.
The reason for this, we surmised, is due to the high number of ghost records.
However when we do looked into it, we got the following results for the tables in the db:
| TableName | record_count | ghost_record_count | version_ghost_record_count | |:--------- |:------------:|:------------------:|:--------------------------:| | Table1 | 19,936,533,500 | 5000 | 473,444,900| | Table2 | 902,990,900 | 100 | 9,143,000| | Table3 | 5,502.563,178 | 800 | 120,117,300| | Table4 | 1,548,468,000 | 500 | 29,863,400| Seeing that the
The reason for this, we surmised, is due to the high number of ghost records.
However when we do looked into it, we got the following results for the tables in the db:
| TableName | record_count | ghost_record_count | version_ghost_record_count | |:--------- |:------------:|:------------------:|:--------------------------:| | Table1 | 19,936,533,500 | 5000 | 473,444,900| | Table2 | 902,990,900 | 100 | 9,143,000| | Table3 | 5,502.563,178 | 800 | 120,117,300| | Table4 | 1,548,468,000 | 500 | 29,863,400| Seeing that the
version ghost records
is so big, we figured restarting the application (not the database) server might clear the records. This did not seem to be the case however. To be honest I am stumped as to what could be the reason for the persistence of the version ghost records.
I'd appreciate any insight into how to troubleshoot/manage this. Or even some additional knowledge or past experience about this topic since I've found very little on the interwebs.
Thank you.
Asked by Dark Star1
(271 rep)
Mar 17, 2025, 09:08 AM
Last activity: Mar 17, 2025, 07:49 PM
Last activity: Mar 17, 2025, 07:49 PM