• I almost completely agree with Gail's comments.

    Shrinking should not be removed. There are situations where a database has suffered exceptional growth, and there is no need to keep that space allocated. For instance if you forgot to schedule log backups, or after a huge data import job, the log file can be huge. Or if you accidentally populated a table with a cross join instead of an inner join, both the data and the log files will be enormous. That is a legitimate situation for reclaiming space.

    Shrinking should not, under any circumstances, be part of regular maintenance. Neither scheduled, nor manually. So it should be deliberately hard to execute. The autoshirink option and the shrink database task in the maintenance wizard should never have been introduced, since, as Steve writes, it is natural to want to reclaim space. Both people with no database background and people with an Access background are bound to think that shrinking is good.

    I don't see much need for optimizing the algorithm. Even with an optimal algorithm, shrinking is still a bad idea. The fragmentation is not the only, nor even the biggest problem. The biggest problem (in my opinion) is that after a shrink, the database will always grow back to its normal working size. That results in many autogrow events - and those are not only slow, but also cause fragmentation at the file system level, which no SQL Server maintenance can undo.

    I am perfectly fine with using shrink when absolutely needed, then reorganizing or rebuilding my indexes to remove the fragmentation. Sure, that will cause some autogrow events (unless I manually grow the files first). So what? The database will ALWAYS grow after a shrink, better to do that while still in the maintenance window and not during business hours.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/