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 reserved data index_size unused
1770892 107544 KB 86392 KB 21048 KB 104 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_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
5 287340088 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 95.9996295953329 10389 1.03946481855809 10799
5 287340088 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 99.9604743083004 2530 1 2530