In the Microsoft SQL Server 2000 Resource Kit, I read about a procedure that checks all databases for options that you should avoid. I understand why I should avoid options such as offline, DBO-use only, statistics off, truncate log on checkpoint, and so on. However, two of the options—autoclose and autoshrink—puzzle me.
Autoclose. Closing a file when it isn't in use sounds like a prudent thing to do. Yet I see files opening and closing all the time in the SQL Server error log, even when I know that I never use the database except for demos. Does viewing a list of databases in Enterprise Manager make the files open? Autoclose makes intuitive sense on databases that I use only occasionally as import staging areas and which I access for only a fraction of the time my online transaction processing (OLTP) database is in use. SQL Server Books Online (BOL) says that autoclose is automatically set to YES for the SQL Server Personal Edition and to NO for all other editions. Has this option been included only for the Personal Edition?
Autoshrink. The autoshrink option also sounds helpful. However, would using autoshrink on my import staging areas, where I bring in quantities of data for short periods of time, keep these areas small for backups?
Autoclose is available on all SQL Server 2000 editions. The amount of memory you save by using this option is relatively small but might be important on memory-constrained systems that use the Personal Edition. However, I wouldn't enable autoclose for the other editions because every time you try to access the database, the query will be slowed by the amount of load time it takes to mount the database. As for the question about your files opening and closing, yes, when you enumerate a database in Enterprise Manager, you access the database.
I don't recommend that you leave on autoshrink for production systems unless you really need to because it might kick in when your system is otherwise busy with production work and slow down the system. However, enabling this option is a good idea for desktop or remote systems that don't get a lot of DBA attention and whose databases could grow out of control before you have time to detect the problem.
