September 26, 2019 at 3:57 pm
JimKern - thanks for posting all the follow-up after answering your own question. good stuff.
October 1, 2019 at 6:01 pm
Crud... ok... it turns out that my other databases are actually being affected. They're just large enough to make it look like the percentage of unused space is quite small. For example, one of my databases is 944+GB... guess how much "only" 2.34% of "Unused" space takes there (+23GB).
It also turns out that about a bit more than half of that was because of a 4GB Clustered Index that gets hit with single row inserts a whole lot.
I've also verified that this problem does NOT occur in SQL Server 2012 (don't have anything to test 2014 with) and that setting the "stricken" database to the FULL Recovery Model does NOTHING to resolve the problem (test that on the 55GB database that only had 8.5GB of actual data after rebuilding all of the indexes and setting the database to FULL a little over a week ago).
I need to do a test to see how this affects true "append only" indexes in a more controlled environment but I've satisfied myself that the only way this can be fixed 100% is by using TF 692, which is what I'm going to try next. I've also written a bit of code that can actually find this problem by index (it's not difficult... anyone could do it).
Like Jim Kern (the OP) did, I'll let you know how it turns out.
Mr. Kern... thank you from the bottom of my heart for bringing this problem up.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply