AutoShrink Set to On

Autoshrink is a feature in Microsoft SQL Server that automatically shrinks the size of a database when it detects that there is a lot of unused space within the file. While this may seem like a useful feature at first glance, it is actually not recommended to use autoshrink in most cases. We will explore some of the reasons why autoshrink can be detrimental to the performance and stability of a SQL Server database.

First of all, it is important to understand that shrinking a database can be a resource-intensive process. When autoshrink is enabled, it will periodically scan the database and try to shrink it whenever it detects that there is a lot of unused space. This can lead to a significant increase in CPU and I/O usage, which can have a negative impact on the performance of the database and the overall system.

Another issue with autoshrink is that it can cause fragmentation of the data within the database. When a database is shrunk, the data is moved around within the file, which can result in a higher number of page splits and increase the fragmentation of the data. This can lead to slower query performance and a decrease in the overall efficiency of the database.

Furthermore, autoshrink can also cause issues with the transaction log. When a database is shrunk, the transaction log needs to be truncated, which can result in a large number of virtual log files (VLFs) being created. A large number of VLFs can lead to performance issues, as it can take longer to traverse the log and find the needed information.

In addition to these issues, autoshrink can also be disruptive to the normal operation of the database. If the autoshrink process is triggered while there are a lot of transactions being processed, it can cause delays and block transactions from completing, which can lead to user frustration and a decrease in productivity.

Overall, it is generally not recommended to use autoshrink with SQL Server. While it may seem like a convenient way to keep the size of the database in check, the negative impact on performance and stability far outweigh any potential benefits. Instead of relying on autoshrink, it is better to implement a proactive database maintenance plan that includes regular index and database reorganization and defragmentation to ensure optimal performance and efficiency.

Recommendation

It is recommended to not use the AutoShrink option in SQL Server. At first glance AutoShrink sounds like a really great option to reduce the size of your database if data is removed. The problem with it is that shrinking your database will cause index fragmentation which may significantly impact performance.

Example Code

The following T-SQL can be used to check to see which databases on your SQL Server have AutoShrink set to on.

SELECT is_auto_shrink_on as AutoShrink, name
 FROM sys.databases
 WHERE is_auto_shrink_on &amp;lt;&amp;gt; 0;

How Do We Fix it?

This can be fixed using the SSMS user interface by right clicking on the database in the object explorer, choosing properties, then clicking the options page in the Properties dialog. One of the top options in the list is AutoShrink, be sure that this is set to FALSE.

Another option is to change the setting with T-SQL.

ALTER DATABASE databaseName SET AUTO_SHRINK OFF;

Replace databaseName with the name of your database.

Contact Info

Stedman Solutions, LLC.
PO Box 3175
Ferndale WA 98248

Phone: (360)610-7833

Download this page in PDF format

Recommendation

Example Code

How Do We Fix it?

Related Links

Contact Info

Leave a Reply Cancel reply