I have a SQL 2008 server which is predominantly used as a development server, in the last two weeks it has been having occasional "fits", I have isolated the cause of these fits as CHECKDB being run almost continuiously, the following log information is logged to the Windows Event Log (Source: MSSQLSERVER, Category: Server):
Event: 1073758961, Message: Starting up database 'DBName1'.
Event: 1073758961, Message: Starting up database 'DBName2'.
Event: 1073759397, Message: CHECKDB for database 'DBName1' finished without errors on 2010-07-19 20:29:26.993 (local time). This is an informational message only; no user action is required.
Event: 1073759397, Message: CHECKDB for database 'DBName1' finished without errors on 2010-07-19 20:29:26.993 (local time). This is an informational message only; no user action is required.
This is repeated every 1-2 seconds untill SQL Server is restarted or the offending databases are detatched.
I initially thought that it was a problem with the databases so I took a backup and restored them to a SQL Express instance, all of the data is in tact, and CHECKDB runs without problem.
The two databases that were causing a problem last week were not being used; so I took full backups of them and detached the databases, this resolved the problem. However at 0100 GMT this morning to other totally unrelated databases started showing the same problems.
There is nothing in the event log to suggest that something happened to the server such as a restart, there are no messages about processes crashing or issues being detected with the storage controller.
Speaking to the owner of the company this computer has suffered from "gremlins" in the past, however advice was taken and the motherboard was replaced and the computer rebuilt, memory and processor are the same.
Stats:
O/S: Windows 2008 Standard Build 6002
CPU: 2x Pentium Dual-Core E5200 @ 2.5GHz
RAM: 2GB
SQL: 2008 Standard 10.0.2531
Edit: someone posted then deleted a comment about AutoClose, it was turned on on the databases affected. It seems that best practice is to disable it so I have done that with the folllowing.
EXECUTE sp_MSforeachdb
'IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')'
I won't know if the problem recurs for some time so I am still open to further answers.
