Enterprise Console: performance issues and database configuration

Enterprise Console can sometimes experience short periods of slow performance which are caused by the activities of the database options 'Autoshrink' and 'Autoclose'. These options automate certain administrative tasks, and are enabled by default when MSDE 2000 is installed. However, these automated actions can sometimes be triggered at inappropriate moments, e.g. when the system is under more intensive use. This might cause a perceptible system slowdown.

If the activation of the options 'Autoshrink' and 'Autoclose' causes problems, or does not comply with your database maintenance policy, then you can disabled them.

You may also experience performance problems with Enterprise Console if the MSDE database has become too large.

What to do

You can disable these options by using SQL Server Enterprise Manager, or by using T-SQL.

Using SQL Server Enterprise Manager

1. Select and expand the SQL server instance that hosts the Enterprise Console database. (The default instance name is SOPHOS.)
2. Expand the Databases node and select the Enterprise Console database. (The default database name for Enterprise Console version 2 is SOPHOS2, and for Enterprise Console version 1 is SOPHOS.)
3. Right-click the database, and select 'Properties'.
4. Select the Options tab, and in the group 'Settings', deselect 'Auto close' and 'Auto shrink'.

Using T-SQL

The commands below assume that the default instance name (SOPHOS) and Enterprise Console version 2 database name (SOPHOS2) are used. If these have been changed, or Enterprise Console version 1 is in use, substitute the names accordingly.

1. Login using an account that has administrative rights for the Enterprise Console database.
2. Execute the following commands:
   
   osql -E -S .\SOPHOS -Q "EXEC sp_dboption 'SOPHOS2' , 'Autoclose' , 'false' "
osql -E -S .\SOPHOS -Q "EXEC sp_dboption 'SOPHOS2' , 'Autoshrink' , 'false' "

If you subsequently want to reinstate Autoshrink and Autoclose, execute the following commands:

osql -E -S .\SOPHOS -Q "EXEC sp_dboption 'SOPHOS2' , 'Autoclose' , 'true' "
osql -E -S .\SOPHOS -Q "EXEC sp_dboption 'SOPHOS2' , 'Autoshrink' , 'true' "