Sometime back I came across a question in the forums where the user was getting this message in the SQL Server Error Log every minute.
CHECKDB for database 'DBName' finished without errors on [date and time]. This is an informational message only; no user action is required. Starting up database 'DBName'
He had not scheduled CHECKDB to run every minute and wanted to know what does this message mean? Quick glance at the informational message clearly indicates that SQL Server is not reporting the results of DBCC CHECKDB. This message is reported in the Error Log whenever a database starts up. This is a new feature in SQL Server 2005 onwards about which I had posted here. The third line in the above message confirms that database is indeed starting up every minute.
Why does the database starts up every minute?
This is because the AutoClose property for that database is set to True.
With this property set to True, when the last user connection disconnects, the database is closed. When a user connects back to the database, the database again starts up and the informational message is logged in the SQL Server Error Log. When a database starts up resources are assigned to it and when it is closed the resources are released. The AutoClose option is useful on a database which is not frequently used like in a database running on SQL Server Express Edition. But if this property is set to True on a busy OLTP database it will have negative impact on the performance of the instance.
Even I had come across few databases in my client’s environment where the AutoClose property was set to True. Since these databases were small in size and did not have much importance, there was no impact. This property can be turned off using Database Properties dialogue in SSMS or using the following query.
ALTER DATABASE [DBName] SET AUTO_CLOSE OFF