Perry Whittle (4/16/2013)
Once you drop the column have you tried querying the system catalogs to see what allocated, used and reserved space sql server thinks the table\index object has?
Not directly after the drop, is it worth repeating the process and noting the stats? It's an overnight restore but I'm not strapped for time.
Here are some figures after I've recreated the column, clearly without the LOB data:
rows reserveddata index_sizeunused
1770892 107544 KB86392 KB21048 KB104 KB
Stats were reporting 0 ghost records present in the table and that the index rebuild didn't have any traction while the column was still dropped, after recreating the column and rebuilding again the fragmentation got cleared up. The below is prior to recreating the column:
database_idobject_idindex_idpartition_numberindex_type_desc alloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_count
5 2873400881 1 CLUSTERED INDEXIN_ROW_DATA 3 0 95.9996295953329 10389 1.03946481855809 10799
5 2873400882 1 NONCLUSTERED INDEXIN_ROW_DATA 3 0 99.9604743083004 2530 1 2530