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.
| Author |
Topic |
|
BRS
Starting Member
3 Posts |
 Posted - 2007-10-09 : 15:10:55
|
| Hi all, looking to get some help on my DB in suspect.I have 5 DBs running fine and one is grayed out and has (suspect) beside it. I think the problem is that PW_IDX.mdf file is corrupt because I could not copy that one when the SQL server was stopped. PW_log.ldf and PW_data.mdf copied fine. I did the sp_resetstatus to reset the satus, which brought up the DB in (Single User). From read up I have to do a dbcc_checkdb but I don't see the DB listed in Query Analyzer. Now I know the easy way is to restore the DB but it looks like the backup have not been able to backup this DB for a week. Are there any steps that I can take to repair this db?ThanksHere is a snip of the log file. 2007-10-09 14:12:26.60 spid12 Recovery of database 'DRIVER_PW' (8) is 22% complete (approximately 83 more seconds) (Phase 2 of 3).2007-10-09 14:12:28.48 spid12 Error: 823, Severity: 24, State: 22007-10-09 14:12:28.48 spid12 I/O error 2(The system cannot find the file specified.) detected during read at offset 0x00000022f20000 in file 'E:\MSSQL\Data\PW_IDX.mdf'..2007-10-09 14:12:28.48 spid12 Error: 3313, Severity: 21, State: 22007-10-09 14:12:28.48 spid12 Error while redoing logged operation in database 'PW'. Error at log record ID (22767:2201:21)..2007-10-09 14:12:28.53 spid12 Error: 3414, Severity: 21, State: 12007-10-09 14:12:28.53 spid12 Database 'PW' (database ID 8) could not recover. Contact Technical Support..2007-10-09 14:13:28.84 spid3 Recovery complete.2007-10-09 14:13:28.84 spid3 SQL global counter collection task is created.2007-10-09 14:13:29.10 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 15:47:09
|
| RunDBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGSon that database.If it is damaged restore from backup.Kristen |
 |
|
|
BRS
Starting Member
3 Posts |
Posted - 2007-10-09 : 16:01:06
|
| I tried it and got the following message. Anything else I can try, before the restore?Server: Msg 926, Level 10, State 1, Line 1Database 'PW' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 16:08:31
|
Sorry, should have thought of that.You can remove the Suspect status from the database, but make sure that users cannot connect to the DB at that time. Don't want to make a bad situation worse!This may help: http://www.sqlservercentral.com/articles/Administering/unmarksuspect/137/I recommend that you make a copy of the database ASAP, so that if any of your endeavours muck things up  you can have another go.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-09 : 23:00:25
|
| Did you see any disk related error in windows event log? Sometimes rebooting can fix db suspect. |
|
|
|
BRS
Starting Member
3 Posts |
Posted - 2007-10-10 : 14:14:16
|
Yes I do see some disk errors but rebooting has not fixed it.I think that my DB is either on a bad spot on the disk or a bad strip set. All drives look good though.Event Type: WarningEvent Source: dmioEvent Category: NoneEvent ID: 35Date: 10/9/2007Time: 2:12:27 PMUser: N/AComputer: OPTICDescription:dmio: Disk Harddisk1 block 358381199 (mountpoint E:): Uncorrectable read errorI have resored and renamed the DB from a week ago to get ppl working, but would really like to get the on weeks worth of work out of it. quote:
Originally posted by rmiao
Did you see any disk related error in windows event log? Sometimes rebooting can fix db suspect.
|
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
What are my options for a DB in Suspect mode
Kristen