Database Marked as Suspect

[dvannoy]

One of my DB's has become marked as suspect. why does this happen? if I stop sql server and restart it, the db comes back online. can someone give me some info or help me go in the right direction to solve this..

Thanks in advance

 

[SQLBill]

Check in Enterprise Manager to see what order your databases were recovered. Note: when SQL Server restarts, it recovers each database. Sometimes they don't recover in the order you expect. I have seen where it accidentally recovered one of my user databases before all the system databases were recovered...then the user db was marked suspect.

There is a way to stop SQL Server from automatically recovering databases. You could use that to have it only recover system databases and then manually (or via a job) recover your user databases once the system dbs are up. (I just don't know the method and will have to see if I can find it.)

-SQLBill

Posting advice: FAQ481-4875

 

[dvannoy]

The db has six tables each table has 170,000 records. what causes the db to get marked as suspect? could there be something wrong with the db?

 

[SQLBill]

Could be something wrong with the db, but I don't really think it's likely. If there was something wrong with the db, then it would come up Suspect even after the restart.

I would also check the SQL Server Error Logs and Windows Event Viewer Logs to see if there were any related error messages.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

According to the BOL, the reasons a db may be marked suspect are:

- not enough disk space to recover the database
- unavailable or corrupt file(s)
- denial of access to database resources by operating system

In your case the first two are unlikely. If one of them was the cause, restarting the system would not have solved the problem

The third option is most likely. Something (anti-virus??) may have been accessing the file when SQL Server tried to recover it. Or SQL Server may have tried recovering it too soon.

-SQLBill

Posting advice: FAQ481-4875

 

[dvannoy]

where in EM do you see what order the recovery is done?

 

[SQLBill]

In the SQL Server Logs. The first one after the restart will show each database recovering.

-SQLBill

Posting advice: FAQ481-4875

 

[dvannoy]

ok,
today it happened again.
udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device S:\Microsoft SQL Server\MSSQL\DATA\SQL_DATA.MDF.
2006-06-23 01:29:35.96 spid52 FCB::Open failed: Could not open device S:\Microsoft SQL Server\MSSQL\DATA\SQL_DATA.MDF for virtual device number (VDN) 1.

what does this mean? listed in the log, it try's to start the DB(suspect) several times.


I have a question. this DB is basically a "Live" DB that's gets data dumped to it every 1min. I backup the TLog every 4 hours and the DB Once a night. could the problem be when it trys to backup the log while data is getting inserted?

any help would be appreciated

 

[dvannoy]

heres another error

Error: 15457, Severity: 0, State: 1
2006-06-22 09:49:04.93 spid107 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2006-06-22 09:49:05.09 spid107 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_msver'.

 

[SQLBill]

Here's the most important clue:

The process cannot access the file because it is being used by another process.)

Something is accessing the database file while it is recovering. Possibilities:

1. antivirus scanning
2. a query
3. a job
4. backups

Go into services, find the SQLSERVERAgent service. Change it to MANUAL startup. Restart SQL Server. Do you still have the same problem? If so, that eliminates #3 and maybe #2. Do you have any other apps that query the database? Make sure they are stopped, that will eliminate #2. If none of that makes a difference, check #1. Antivirus should be set to ignore .mdf and .ldf files. Then check to see if the network/system people have a backup job running that could be backing up that file.

-SQLBill

Posting advice: FAQ481-4875