4

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.

3 Answers 3

5

Check AutoClose property of our databases. Turn it OFF

1
  • 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. Jul 21, 2010 at 9:25
2

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;

1
  • 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. Jul 20, 2010 at 16:13
-1

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.

3
  • The two databases affected have not been restarted, nor has the server. Jul 20, 2010 at 10:10
  • AutoClose was my first guess but i've deleted that message
    – Sergey
    Jul 20, 2010 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. Jul 20, 2010 at 16:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .