Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Check AutoClose property of our databases. Turn it OFF

share|improve this answer
    
Thanks Sergey, as I mentioned to Remus it would make sense if this was the problem. I am monitoring the server at the moment, will post back in about a week and accept an answer. –  Richard Slater Jul 21 '10 at 9:25
add comment

AUTO_CLOSE:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

ALTER DATABASE DBName1 SET AUTO_CLOSE OFF;

By default databases created on Express instances have the AUTO_CLOSE ON;

share|improve this answer
    
That would make a lot of sense, some of these databases have been migrated from several different versions of SQL including Express SKUs. I have run the script I posted above, we will see how it goes. Thanks for your answer. –  Richard Slater Jul 20 '10 at 16:13
add comment

It's normal process for any SQL database after server restart....

SQL Server checks last transactions applied in database (MDF) with last registered in transaction log (LDF). It can apply transactions from log to DB if necessary.

share|improve this answer
    
The two databases affected have not been restarted, nor has the server. –  Richard Slater Jul 20 '10 at 10:10
    
AutoClose was my first guess but i've deleted that message –  Sergey Jul 20 '10 at 13:11
    
AUTO CLOSE was possibly a better answer, I am not sure why you changed your mind? It is a normal process when a server restarts but the server is not restarting every 1-2 seconds so I don't understand the relevance of your answer. –  Richard Slater Jul 20 '10 at 16:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.