OpenUtilities Wiki Disable Auto Shrink to avoid performance degradation

Disable Auto Shrink to avoid performance degradation

	Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.140
	Environment:	N/A
	Area:	Database Platform Support
	Subarea:	N/A

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

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

Option 2

Connect to the SQL Server using SQL Server Management Studio (SSMS).
Click the New Query button.
Paste the following into the new query window:

ALTER DATABASE yourdb SET AUTO_SHRINK OFF
Replace "yourdb" with the actual name of the database.
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.