Why Should You Always Turn Off Database Auto Shrink Property?

SQL Server Auto Shrink is database property that allow database engine to automatically shrink database files if its value set to ON/True. Shrinking a database is not a good practice because it is very expensive operation in terms of I/O, CPU usage, locking and transaction log generation. Database auto shrink in SQL Server also causes your Indexes to be fragmented because it runs frequently.

Database Auto Shrink or Manual shrink operation runs at database file level. It is not recommended to shrink your data files except in case of the few exceptions like data deletion.  You can run shrink operation if you have deleted some portion of the data to reclaim that space. As this shrink operation can cause index fragmentation so make sure to rebuild your fragmented indexes post performing the shrink operation. Shrinking the log file may be necessary in some cases if your log file is full and need to clear some space. However, shrinking the log file should not be part of any regular maintenance activity and should be manually done whenever it will require.

It is always recommended to never run shrink operation as part of maintenance activity even you should avoid running shrink operation manually if you are doing it. If it is needed to run shrink operation, make sure rebuild your indexes. You should always turn off SQL Server Auto Shrink  for all databases to avoid any future performance issue on your database. By default, SQL Server keeps auto shrink turn off on SQL Server instances.

There will be a bad impact on performance if you turn on database auto shrink and autogrowth settings together for any database. Most of the database files have some value to autogrowth setting enabled for the databases or we set the database size to the optimum value keeping some room in the data files to grow and avoid frequent auto growth events. If we will enable SQL Server auto shrink property for such databases, it will shrink the data files and reclaim the free space that we have kept intentionally to avoid autogrow events. In that case, both operations will be performed frequently auto growth and then SQL auto shrink that ultimately lead to the system level fragmentation which causes severe performance issue as well.

So in short, Autoshrink should not be turned on for any databases. This is bad for several reasons that I have concluded in below points.

1. Database Auto Shrink or Manual Shrink causes index fragmentation that will reduce the database performance.
2. Shrink operation takes lot of IO and CPU resources. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
3. Repeatedly shrinking and growing the data files will cause file-system level fragmentation, which can slow down performance. It wastes a huge amount of resources, basically running the shrink algorithm for no reason.
4. If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. So Autogrow and Autoshrink together can seriously reduce your system performance.
5. SQL Server Auto Shrink and autogrow settings must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.

Autoshrink doesn’t work like if the threshold is hit, auto shrink SQL Server operation will start to reclaim space. It uses round robin method to shrink databases if you have multiple databases are set to use auto shrink. SQL Server shrinks a database if needed. Then, it waits several minutes before checking the next database that is configured for auto shrink so your database will need to wait for his turn to execute auto shrink operation.

How to Enable or Disable Auto Shrink in SQL Server?

We can turn on or off database auto shrink SQL Server configuration using GUI and T-SQL both ways. Both are easiest method. Let us first start with GUI method.

Right click on your database for which you want to enable or disable auto shrink and choose Properties. You will get below Database Properties window. Click on Options from left side pane of this window. You can see Auto Shrink option is showing in right side pane. If you want to turn it on, click on drop down of this option and select True.

Below is the T-SQL code to enable or disable the Auto_Shrink option for any database. Change the database name with your database name and execute it.

--Set Auto Shrink ON or True
ALTER DATABASE TechYaz SET AUTO_SHRINK ON
--Set Auto Shrink OFF or False
ALTER DATABASE TechYaz SET AUTO_SHRINK OFF

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Reference : AUTO_SHRINK database option in SQL Server

Read More:

• Should We Shrink SQL Server Databases or Files?
• Understanding AutoGrowth settings and its impact on Performance
• SQL Server Backups for AlwaysOn Availability Databases
• What is Instant File Initialization and how to enable it?