XML is 17GB on disk... 4MB when cast as varchar(max)

Question

XML is 17GB on disk... 4MB when cast as varchar(max)

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQLdude412 SSC Veteran Points: 209 More actions · Answer 1

I have now confirmed that this is a ghost record cleanup issue. Running a select on sys.dm_db_index_physical_stats for that object returns over 1 million version_ghost_records associated with the LOB_DATA on the clustered index.

Upon further investigation I have discovered there is a bug in SQL Server SP3 related to ghost cleanup issues, documented here however I am not sure this bug is affecting us. (Although it does affect SQL 2008 SP3 which we are running)

I have narrowed it down to a particular service that is running on the application server and is maintaining a DB connection and executing a SELECT query periodically (polling) but the query is on a completely different database.

When I stop that service, and the associated DB connection is dropped, the ghost records start to disappear and the space used by the DB steadily drops, taking about 1-2 minutes to go from 18GB to 5MB.

This service appears to have an open transaction, although nothing is returned by DBCC OPENTRAN, I do see it in sys.dm_tran_active_transactions with a transaction_state of 2 (active).

When I check sys.dm_tran_locks the only thing I see is a shared lock on its own database (not the one with the XML column).

Both databases are on the same instance, and the files are on the same physical disk. Both databases have READ_COMMITTED_SNAPSHOT enabled.

At this point I'm stumped as to why this transaction would block the ghost cleanup process from cleaning up the ghost records in the other database. Any additional help at this point is very much appreciated. And thanks again to those who have helped me this far.

SQLdude412 SSC Veteran Points: 209 More actions · Answer 2

Final update: I've identified that the version_ghost_record_count values are increasing in all tables in all databases on the SQL Server instance, not just the table with the XML data. I consider this issue to be resolved (by killing the process with the open transaction), however I'm going to post a question to MS because I'm curious about this behavior (again, there are no locks that I feel should be blocking ghost cleanup). Thanks again everyone.