Removing Auto-Shrink From Maintenance Plans

A while back I submitted an item over at Microsoft Connect regarding the fact that users were allowed to include an auto-shrink task inside of their maintenance plans. I was very polite in asking:

“Please remove the ‘Shrink Database’ option for maintenance plans. Or, rename it to ‘auto fragment’, so that the implications of its usage would be more clear to novice DBA’s.”

All too often I hear and see DBAs using auto-shrink as a part of their routine maintenance, and they don’t truly understand the implications of their actions. As hardware gets cheaper and more powerful the use of auto-shrink becomes less of an issue for folks working with small-ish sized databases as the fragmentation can be masked by a boatload of memory, faster CPUs, and blazing fast disks.

The trouble really kicks in when these same DBAs start working with databases that aren’t so small, and the fragmentation is no longer hidden by the beefier hardware. The trouble is that these larger databases also require more time to work with, and you don’t want to spend hours, or days, waiting for things like shrinks and rebuilds to happen.

So I filed the Connect item to have the option removed, or to have a better explanation of what happens under the hood when a shrink operation occurs. And recently I noticed that the item was marked as “Resolved” and “Fixed”.

I take “Resolved” and “Fixed” to mean that auto-shrink will be removed from a future version of SQL Server. Otherwise what is the point of marking something as “Fixed”, right? I am hopeful that the RTM version of Denali would have this fixed, but more than likely it is the next version after that one.

And this is the part where Buck Woody (blog | @buckwoody) would raise his hand and tell us that SQL Azure doesn’t come with an auto-shrink, so you never have to worry about this problem in the Cloud.

So I wanted to say “thanks” to Microsoft for listening on this item, and making the effort to have their products serve the needs of their customers.

2 thoughts on “Removing Auto-Shrink From Maintenance Plans”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.