sqlGDBA (12/5/2012)
Doesn't the fact that you have 80% fragmentation mean that you're already using a significant amount of space over and above what is actually needed ? using a fill factor of 80 would only increase your footprint by 25%, so I am not sure how that would cause the drive to be filled up. I understand the log filling up, but switching to a simple recovery model may be another option to try during the index rebuilds.I understand and appreciate the point of seeks vs. scans, but the massive amount of space saving(in some environments) itself may be enough to justify the defrag. I know that SAN space is pretty expensive and once you do the math and see the cost you are incurring with the bloated table(at least about 400 GB, perhaps?) you can make that call. I know that in our shop, another 400 GB can be quite a bit $$ in savings 🙂
i never understood fragmentation as a cost in space so much as just bad organization. the trouble is when i run my indexing script that shows what index on what table is at what % and what page count... this table lists 6 times with the same index name with different %'s. that's a new strange issue i'll track down. and on a 1.2 tb drive, a table at 1.17 tb's does mean 25% is an issue currently. oh the webs we weave.
.