Select Page

AutoShrink Set to On

AutoShrink

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.

</pre>
SELECT is_auto_shrink_on as AutoShrink, name
 FROM sys.databases
 WHERE is_auto_shrink_on <> 0;
<pre>

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.

AutoShrink

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.

 

Related Links

Submit a Comment

Your email address will not be published. Required fields are marked *

To prove you are not a robot: * Time limit is exhausted. Please reload CAPTCHA.