sql server log

[nirmalsp]

Hi

There is a message loging to my sql server log file
as

"2002-02-05 21:02:02.68 spid9 Closing file e:\MSSQL7\data\Efficiency_Data.MDF.
2002-02-05 21:02:02.82 spid9 Closing file e:\MSSQL7\data\Efficiency_Log.LDF.
2002-02-05 21:23:59.70 spid9 Opening file e:\MSSQL7\data\Efficiency_Data.MDF.
2002-02-05 21:23:59.70 spid9 Starting up database 'Efficiency'.
2002-02-05 21:23:59.78 spid9 Opening file e:\MSSQL7\data\Efficiency_Log.LDF.
2002-02-05 21:24:00.12 spid9 Closing file e:\MSSQL7\data\Efficiency_Data.MDF.
2002-02-05 21:24:00.20 spid9 Closing file e:\MSSQL7\data\Efficiency_Log.LDF.
2002-02-05 21:24:00.28 spid9 Opening file e:\MSSQL7\data\Efficiency_Data.MDF.
2002-02-05 21:24:00.28 spid9 Starting up database 'Efficiency'.

why is this database is always opening & closing & restarting.

pls. do help me with this.
can this cause for sql server performance

Thanks

 

[tlbroadbent]

Check the autoclose database option. Yes, it can negatively impact performance. In general, it is best to set autoclose off on Servers and set it on on Desktop systems such as MSDE.

The following is from SQL BOL.

autoclose

When true, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to true for all databases when using SQL Server Desktop Edition, and false for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When false, the database remains open even if no users are currently using the database.

The autoclose option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The autoclose option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

The status of this option can be determined by examining the IsAutoClose property of the DATABASEPROPERTY function. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.