Database Marked Suspect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Marked Suspect

Author

Topic

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-11 : 07:15:08

SQL Server 2000 on Windows 2003 32-bit

Our major database has been marked suspect (250+ GB)

Our main drive is 1 TerraByte partitioned C 90 GB, D rest.

F-Drive 300 GB for Log files.

Help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 07:16:04

Do you have a backup?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-11 : 07:39:34

Can you check if all your storage devices are online? Any related messages in the error log?
Try resetting the flag using sp_resetstatus 'mydb'

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-11 : 09:35:00

I was missing one of my disk drives this morning (contained tranaction logs). Looks like they installed new disk drivers on the server.

Rebooted and the drive showed up.

Tried the sp_resetstatus stored procedure.

Got error 3414 Severity 21, state 1 error (could not recover).

If ask to restore old drivers, and tried again would that have a chance of working?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 09:40:38

I doubt it.
They did this while the server was active?
At least you know who owes you a drink.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-11 : 12:01:15

Do you have a backup? Please, please tell me you do.

If not, please post the following:
The exact state of the database (state and state2 in sysdatabases)
All errors in the error log relating to that database.

--
Gail Shaw
SQL Server MVP

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-11 : 12:21:16

Yes, have backup 1 week old. We are restoring from it. I gave up on other methods. May take a day or two to restore. Hopefully it will be successful.

Thanks for quick response from you all.

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-11 : 12:33:09

I did find out that they installed a new video driver, not disk driver. Would that have any impact?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-11 : 12:53:44

Nope, no effect whatsoever.

Suspect state is caused when a rollback or crash recovery fails due to corruption in the database.

--
Gail Shaw
SQL Server MVP

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-12 : 04:47:35

Recovery from backup completed. Rebooted. Suspect again.

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-12 : 05:13:50

I was thinking, after recovery complete, would it allow dbcc checkdb (repair) without marking it suspect, if reboot made it suspect?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-12 : 10:00:57

REstore from backup onto a new IO subsystem and get the server engineers to figure out what's wrong with the current one.

Suspect state is caused when a rollback or crash recovery fails due to corruption in the database. A reboot requires SQL to run crash recovery. So something is corrupting that DB fast.

--
Gail Shaw
SQL Server MVP

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-12 : 11:07:05

I found this code on Google, but was for Sql Server 2005.
A fellow worker found some cmd line code to start SQL Server 2000 in Emergency Mode
Could that be incoprorated into it?

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-13 : 02:21:24

That's by no means guaranteed to even work. It's the absolute, totally, finally, last resort when you have no backup and nothing else works.

There are 2 possibilities here.
1) The backup is damaged
2) The drives are corrupting the database after restore.

When did you last run CheckDB?
Can you restore to another server (with a completely independent IO subsystem) and see if the same thing happens.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-10-13 : 03:19:23

I too would definitely want to restore it somewhere else. You can check it there too. Perhaps run a hardware test on the main machine to see if that suggests any hardware faults?

"Recovery from backup completed. Rebooted. Suspect again."

Some particular reason you rebooted at that point?

"DBCC checkdb('yourDBname')"

I suggest you use this instead:


DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS

so you don't get all the useless wibble, and you will see every error message (which may be important in deciding how much data you will lose and what actions to take).

As this is in SQL 2000 forum I'm assuming you are using that version, for SQL 2008 I use:


DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY

to check my databases.

Don't run the last three steps, including REPAIR_ALLOW_DATA_LOSS, without checking the output of the CHECKDB first - i.e. don't just run all those 6 commands as a single batch!

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-17 : 05:44:20

Finally we did get it up from a backup a month old. Thanks everyone for you help.

Mike

Kristen
Test

22859 Posts

Posted - 2011-10-17 : 07:57:20

Something caused the database to become corrupted. Most probably a hardware fault. If that's the case, and not fixed, it will reoccur - so best to make sure the hardware is "faultless"

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-17 : 08:40:59

We ran chkdsk on all the hard drives. Is there any other way to checkout the hardware?

Kristen
Test

22859 Posts

Posted - 2011-10-17 : 08:45:00

Needs a soak-test I think, which most probably means having the hardware off-line.

Checking event-log for anything out-of-the-ordinary might help - if you have messages in there then you could attend to the cause of those.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-18 : 10:15:32

Could also be there was lingering corruption in the backed up database (and that they're not bothering with integrity checks) that got caught by a rollback/crash recovery.

--
Gail Shaw
SQL Server MVP

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-18 : 11:36:31

Thanks,

I will look into it.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources