SQL Server AUTO_SHRINK

Question

You have to carefully evaluate setting this option for the databases in a SQL Server instance. Frequent grow and shrink operations can lead to various performance problems.

Referring from this doc:https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/considerations-autogrow-autoshrink

With these considerations, did I still need to set AUTO_SHRINK?

Answer 1

Hi @Riley

Due to the health and performance of the database itself, this setting is not recommended for multiple uses. For below reasons:

(1) If you do not find the cause of self-growth, thereby avoiding running out of space in the first place, although you can temporarily shrink the file size with the DBCC SHRINKFILE function, it is still possible for the database to grow next time. Shrinking a database is only a palliative.

(2) Data file shrinkage will bring more fragmentation to the file.

(3) The impact on performance is particularly severe in heavily loaded systems. They are actions that should be avoided as much as possible rather than encouraged.

Therefore, for a busy database, the recommended setting is not to use the auto-shrink function.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

did I still need to set AUTO_SHRINK?

?? You never need to set the AUTO_SHRINK option.

Answer 3

AUTO_SHRINK is considered evil. Never turn on this option!

Overall, shrinking is an exceptional operation which you only perform when you know that the file in question will not grow back to its earlier size. For instance, you take a copy of production to dev, and delete 90% of the data. This is a good use case for shrink.

On the other hand, running close to capacity on the disk drive is not.

Answer 4

As mentioned by Erland , AUTO_SHRINK is not recommended . see the considerations mentioned in the documetation

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/considerations-autogrow-autoshrink#considerations-for-auto_shrink

Considerations for AUTO_SHRINK

AUTO_SHRINK is a database option in SQL Server. When you enable this option for a database, this database becomes eligible for shrinking by a background task. This background task evaluates all databases that satisfy the criteria for shrinking and shrink the data or log files.

You have to carefully evaluate setting this option for the databases in a SQL Server instance. Frequent grow and shrink operations can lead to various performance problems.

If multiple databases undergo frequent shrink and grow operations, then this will easily lead to file system level fragmentation. This can have a severe impact on performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.

After AUTO_SHRINK successfully shrinks the data or log file, a subsequent DML or DDL operation can slow down significantly if space is required and the files need to grow.

The AUTO_SHRINK background task can take up resources when there are many databases that need shrinking.

The AUTO_SHRINK background task will need to acquire locks and other synchronization that can conflict with other regular application activity.

Consider setting databases to a required size and pre-grow them. Leave the unused space in the database files if you think the application usage patterns will need them again. This can prevent frequent shrink and growth of the database files.