AutoShrink Set to On



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 <> 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.


Replace databaseName with the name of your database.


Related Links

Leave a Reply

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


To prove you are not a robot: *