This week's topic is data file shrinking. I've seen lots of mis-information in the last few weeks and I've had a bunch of questions about it. First up is auto-shrink.
In my opinion, this feature causes way more problems than it solves (in fact, I can't think of a single problem it solves) and should be removed from the product. Remember I'm talking about auto-shrink, not manual shrink. Post a comment or drop me a line if you think there's a scenario where it's required.
So - if that hasn't convinced you to make sure its turned off, I don't know what will. For databases created on SQL Server 2005, the AUTO_SHRINK option is off by default. You can use ALTER DATABASE yourdb SET AUTO_SHRINK OFF for all others, especially the MODEL database, so new databases don't inherit the setting unwittingly.
Next up - a look inside the algorithm for 2005 and how you may see the run-time of shrink increase unexpectedly, depending on your schema...
Every time I encounter a best-practice recommendation, be it a new idea or old-hat common sense,...
Turn if OFF? Never! Why.. how can I justify a higher budget, if I can't randomly generate system slowdowns? Why, I put Auto Shrink right up there with 45 minute Checkpoint recovery times, using Audit/Modification times as my clustered primary keys, and using lots of stored procedures which access out-of-scope temporary tables.
I agree 110% Paul. I have been preaching this for years and include it with each presenation on DB maintenance.
Late night blog post to round out spring-break vacation... A number of customers I’ve spoken to in the
Tempdb database storage configuration q Determine total size of data and transaction log required for
NOTE: This section has been updated to include special considerations for running SQL Server on Windows