How often to shrink database
-
Coming into a new company, they have a maintenance plan to shrink their main database every night. Is this a good practice or will this fragment the indexes? What is a reasonable frequency for shrinking a production database?
Many thanks,
EB
-
Answer: 0
* Noel -
So, why wouldn't you ever shrink a database? If I'm changing a current maintenance plan, I'll need a logical reason for it since I'm the new person.
Elizabeth
-
Sorry I just lost my post. Here are *my* reasons:
-If you shrink it all work performed by previous reindexing is spoiled( pages are moved in the file unless you use the "truncateonly" option which it is almost never effective)
-If you shrink it is because it grew, right? chances are that you do need the space.
-After several cycles of shrink/expand your file gets physically fragmented with all the take-release-take cicles.
-If grows after you shrink it you are probably experiencing autogrows which slows your server down and if they happen in the middle of your production day your users may not be happy with the performance.
- Last and not least none of that would happen if you simply don't shrink it
Cheers,
* Noel -
Thanks very much, Noel. All that makes sense; I just have to have a good reason to change an existing practice...
Happy coding,
Elizabeth
-
Here is a post on the subject from the SQL Server Storage Engine team -- the folks at Microsoft who built SQL Server:
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
They vote against it, too.
Richard
-
Only time I shrink is if for some reason I have been in a database causing it to grow more than normal. For example during a change to a table where I migth have caused it to hold two copies of a large table during the change.
-
Thanks, all for your good advice. This is what I thought but just needed justification for my thoughts.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply