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.

We have SQL Server 2008 Express installed on a Windows 2003 server. Looking at the event log, one of the databases appears to be restarting anywhere from every couple seconds to every 15 to 30 minutes.

This server hosts about half a dozen databases; the problem is with only one. This database is also the onle one comprised of multiple schemas (not just dbo).

There are thousands of events going back several months. There doesn't seem to be any affect on the website using the database, nor does any data appear to be corrupted or compromised. I'm not a DBA, so I don't even know where to look for causes to this. Any suggestions?

Edit: This is a sample entry from the event viewer:

Date: 6/1/2010 Time: 11:20:52 AM Type: Information User: N/A Computer: MyServer Source: MSSQL$SQLEXPRESS Category: Server Event ID: 17137 Description: Starting up database 'MyDatabase'.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Your database is set to AUTO_CLOSE ON. Every time no user is connected to it, it closes. Every time a new connection uses the database, it starts. These is likely a database created on SQL Express and then attached to a non-Express SKU, because Express creates the databases as auto-close on by default.

Simply set it to AUTO_CLOSE OFF:

ALTER DATABASE <dbname> SET AUTO_CLOSE OFF;
share|improve this answer
1  
Actually, looking at the 'MSSQL$SQLEXPRESS' in the error, this is a SQL Express SKU, so it is normal for the database to be auto_close. I would still turn it off. –  Remus Rusanu Jun 2 '10 at 1:02
    
Looks like that did it, thanks. –  Michael Itzoe Jun 7 '10 at 14:17

To check for corruption, open SQL Server Management Studio and open a query window. Run the following command to check for corruption.

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

For more details about identifying database corruption.

If the database come up clean, share the the log messages in full detail. Also look for information in the SQL Server Logs in Management Studio. There may be clues to what is causing this problem. It's actually very neat because you can view the Windows Application event logs next to the SQl Server log events from the Management Studio.

share|improve this answer

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.