Home / server / Questions / 162135
Accepted
Richard Slater
Asked: 2010-07-21 01:49:28 +0800 CST

How to diagnose repeated "Starting up database '<dbname>'"

  • 772

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.

windows-server-2008 sql-server hardware

3 Answers

  1. Best Answer

    Check AutoClose property of our databases. Turn it OFF

    • 5

  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;

    • 2

  3. 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.

    • -1