We were dealing with a client who had a very large database that was out growing their disk space. Initial plan was to shrink the database to regain the almost 18GB of space that was not used anymore. Started the shrink and found it was taking a very long time to complete. Even worse when it completed performance was worse than before – even though the database was now smaller. Strange!
It was at this moment that something snagged in my brain and I remembered something I had read about NEVER having AutoShrink and ONLY AS A LAST RESORT do a shrink. I found the article and thought I would share it here:
From SQL Server Shrink
Shrinking of data files should be performed even more rarely, if at all. Here’s why – data file shrink causes *massive* index fragmentation.
The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.
Valuable lesson learned!