Background

Manual and automatic shrinking of SQL database data files causes index fragmentation, which leads to poor performance.  Having the Auto Shrink property and the Auto Growth property enabled for a database can result in frequent shrink-grow cycles which causes file system fragmentation, adding to performance issues.  To avoid this situation, disable Auto Shrink on SQL Server databases such as the project databases for Promis.e and Bentley Substation.  This will stop automatic shrinking and further index fragmentation but does not fix existing fragmentation.  To correct the index fragmentation use the method described in Rebuild indexes in the SQL project database after disabling Auto Shrink and defragmenting the disk.

Note: the scripts that create and update the project database have been changed to set the AUTO_SHRINK property to OFF. This change was made for version 08.11.12.120, so depending on the history of the database, Auto Shrink may already be disabled.

Steps to Accomplish

I - Disable Auto Shrink

Option 1

1. Connect to the SQL Server using SQL Server Management Studio (SSMS).
2. In the Object Explorer, expand Databases.
3. Right-click the database to be affected and select Properties.
4. On the Options page, set the Auto Shrink property to False, click OK.

Option 2

1. Connect to the SQL Server using SQL Server Management Studio (SSMS).
2. Click the New Query button.
3. Paste the following into the new query window:
   
   ALTER DATABASE yourdb SET AUTO_SHRINK OFF
   
   
4. Replace "yourdb" with the actual name of the database.
5. Click the Execute button.

II - Defragment the physical disk

This step may be optional, depending on the level of fragmentation that exists.  It would need to be done when users do not need to access the databases.

1. Back up all databases that have files located on the fragmented disk(s).
2. Stop the SQL Server service along with its related services
3. Run the defragmenter program.
4. After the disk(s) have been defragmented, restart the SQL Server services.

III - Re-index the database

See Rebuild indexes in the SQL project database

This article based on the following works:

Turn AUTO_SHRINK off!!

Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server

External Links

How to: Stop an Instance of SQL Server (SQL Server Configuration Manager)

Keywords: autoshrink