SQL Server misunderstanding: database file shrinking on the last 30 days will not affect performance

Misunderstanding #9: database file shrinking does not affect performance

Error!

The only condition that shrinking database files does not affect performance is that the truncateonly option is specified when there is space at the end of the file.

The process of shrinking files has a significant impact on performance. This process requires moving a large amount of data to cause a large amount of Io. This process will be recorded in the log, resulting in log spikes, it also occupies a large amount of CPU resources.

Not only does the contraction affect the performance, but it also affects the performance after the file is shrunk. A large number of logs generated by the contraction will be transferred by transaction logs, images, and copies can be executed repeatedly. When there is not enough space, the file also needs to be initialized with 0 to affect the performance (unless you do not need to fill in the initialization option ).

This is not the worst case. The worst result is that file shrinkage causes a large number of index fragments, which affects the performance of scan operations.

Unfortunately, the database is shrunk.CodeIt's not what I wrote (if I did, I wouldn't allow this mechanism in the first place), so the only thing we can do is accept this operation.

If you want to find a method to replace database file shrinking, please read this blog post: Why you shoshould not shrink your data files, or you should plan the file at the beginning: importance of data file size management. Also, this article: TGIF time warp.

Remember this:

• Shrinking data files is evil.
• Database shrinking is more evil
• Automatic contraction is nothing more than evil.

Simply saying "no" can keep us away from the troubles.