Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered indexes, implementing row or page compression, etc.
However, there are a few cases where I was able to reclaim even more space by executing DBCC CLEANTABLE. With hundreds of databases in my environment, it's not possible to know what users do in each one and it's completely acceptable that there will be changes involving the dropping of fixed length columns. I've typically found these opportunities by looking at row counts vs. page counts in some object space utilization scripts I've written. I'd like to take this a step further by attempting to automate the detection of these sorts of scenarios.
What I'd like to know is if anyone out there is actively monitoring for these sorts of opportunities and if so, what do you look for specifically?
My thoughts were to write something along the lines of gathering the max and min size of a row, the number of rows in the table, the number of pages allocated, and the number of pages used, then doing some basic math to log results that are well outside what would be "expected".