SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Database Marked Suspect
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/11/2011 :  07:15:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/11/2011 :  07:16:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

Cindyaz
Yak Posting Veteran

USA
73 Posts

Posted - 10/11/2011 :  07:39:34  Show Profile  Reply with Quote
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'


Edited by - Cindyaz on 12/06/2011 04:21:06
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/11/2011 :  09:35:00  Show Profile  Reply with Quote
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?

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/11/2011 :  09:40:38  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/11/2011 :  12:01:15  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 10/11/2011 12:57:45
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/11/2011 :  12:21:16  Show Profile  Reply with Quote
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.
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/11/2011 :  12:33:09  Show Profile  Reply with Quote
I did find out that they installed a new video driver, not disk driver. Would that have any impact?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/11/2011 :  12:53:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/12/2011 :  04:47:35  Show Profile  Reply with Quote
Recovery from backup completed. Rebooted. Suspect again.
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/12/2011 :  05:13:50  Show Profile  Reply with Quote
I was thinking, after recovery complete, would it allow dbcc checkdb (repair) without marking it suspect, if reboot made it suspect?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/12/2011 :  10:00:57  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/12/2011 :  11:07:05  Show Profile  Reply with Quote
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


Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/13/2011 :  02:21:24  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 10/13/2011 02:23:52
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/13/2011 :  03:19:23  Show Profile  Reply with Quote
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!

Edited by - Kristen on 10/13/2011 03:20:59
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/17/2011 :  05:44:20  Show Profile  Reply with Quote
Finally we did get it up from a backup a month old. Thanks everyone for you help.

Mike
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/17/2011 :  07:57:20  Show Profile  Reply with Quote
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"
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/17/2011 :  08:40:59  Show Profile  Reply with Quote
We ran chkdsk on all the hard drives. Is there any other way to checkout the hardware?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/17/2011 :  08:45:00  Show Profile  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/18/2011 :  10:15:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 10/18/2011 :  11:36:31  Show Profile  Reply with Quote
Thanks,

I will look into it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000