SQLExpress Autoclose and EventLog

[JohnH]

With SQL Express (and MSDE before that) databases Autoclose is enabled by default. Every re-connect is logged as 'Information' in server EventLog. In a small local network I see that while client application is executing there is a re-connect every 5 seconds. In a day this could result in hundreds/thousands of events logged and every day there is a server error about EventLog being full. With stored procedure sp_dboption Autoclose can be disabled. Does this cause other problems? Are there other options? Like:
- Can only 'Information' logs be turned off from SQL Server? (I didn't find it with sp_configure)
- Can Autoclose timeout be configured?
- Is the problem with the implementation of that application (not mine) or SQL Server?

Input appreciated.

 

[Gruntie776]

As you say the Autoclose option is on by default on the express version and off by default for all other versions (same for MSDE). This behavior is designed to release hardware resources to the machines running Express when the DB engine is inactive and makes sense in a development environment.

As you are in a (busy?) production environment I would go ahead and turn it off if you don't need these resources for any other purposes. If there are other resource hungry services operating off the server you may have to do some manual balancing of the available resources to keep everything running smoothly.

(For example if you had a mailserver on the same machine it might be choked while the db is open but recovers in the idle periods. Setting autoclose to off would mean it may never have a recovery period. Therefore careful monitoring (before and after) of all services on the server would be required.)