Autshrink may have DEEP impact in performance because of several reasons:
- no log writing while shrinking the log file
- fragmentation of log file
Link:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/8fcf9749-8645-4bc0-a8ca-78af56f352ca
* * *
The major problem with the Shrink operation
is that it increases fragmentation of the database to very high value.
Higher fragmentation reduces the performance of the database
as reading from that particular table becomes very expensive.
Look at irony of the Shrinking database.
One person shrinks the database to gain space (thinking it will help performance),
which leads to increase in fragmentation (reducing performance).
To reduce the fragmentation, one rebuilds index,
which leads to size of the database to increase way more than the original size of the database
(before shrinking).
Well, by Shrinking, one did not gain what he was looking for usually.
Rebuild indexing is not the best suggestion as that will create database grow again.
Link:
http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/
* * *
Shrinking a database can cause your Indexes to be fragmented, especially if it happens a lot.
I never shrink my databases unless I have a huge deletion of data,
and I know that the data won't come back.
That's a pretty rare event,
and when it does happen I run the shrink operation manually
and rebuild my indexes after.
Link:
http://blogs.msdn.com/b/buckwoody/archive/2009/07/01/sql-server-best-practices-auto-shrink-should-be-off.aspx
* * *
Benefits of not automatically shrinking files:
– Eliminates grow and shrink syndrome
– Reduces physical file fragmentation
– Reduces resources used for these operations, allowing more important tasks to use them
Link: www.sqlservercentral.com
Article: Best Practices Every SQL Server DBA Must Know - by Brad M McGehee - SQL Server MVP
* * * * *