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)
 Suspect Databases after Power Cut?

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2004-08-18 : 02:57:52
Got a problem with some DBs at a client site. We had some bad weather in the area yesterday, and overnight, so probably some power problems (they are way WAY in the sticks, although they do have a proper server room, hefty Sun kit and the like, proper UPS etc. but maybe not on this server, or it went U/S too ...)

(I've changed name of Application Database to "MyDB" and client's name to "CLIENT")

distribution, MyDB and msdb are "suspect". SQL Agent is not running

The server is used only to pull data from Oracle, and to push that on to another server. Thus just one SQL task, which runs hourly. We don't use replication (but we did install a trial replicated DB on this server a while back)

I have four recent SQL logs
Current 2004-08-18 07:23 (time now, here, is 07:49)
Archive#1 2004-08-18 04:24
Archive#2 2004-08-18 04:21
Archive#3 2004-08-18 04:00
Archive#4 2004-08-17 13:00
Archive#5 2004-07-17 12:57

Last entry in Archive#4 log is:

2004-08-17 13:00:07.13 Log backed up: Database: ...

Next log, Archive#3, starts

2004-08-17 13:49:31.97 Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

2004-08-17 13:49:37.10 Starting up database 'MyDB'.
...
2004-08-17 13:49:39.57 163 transactions rolled forward in database 'MyDB' (5).
2004-08-17 13:49:39.61 0 transactions rolled back in database 'MyDB' (5).
2004-08-17 13:49:39.80 Recovery is checkpointing database 'MyDB' (5)
...

Last entry in Archive#3 log is:

2004-08-18 04:00:07.28 Log backed up: Database: ...
(No tidy shutdown)

Next log, Archive#2, starts

2 lines of garbage

2004-08-18 04:21:14.09 Microsoft SQL Server 2000 - 8.00.760 (Intel X86)

Last entry in Archive#2 log is:

2004-08-18 04:21:14.52 Attempting to initialize Distributed Transaction Coordinator.

then 8 lines of garbage
(No tidy shutdown)

Next log, Archive#1, starts

2004-08-18 04:24:15.87 Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
...
2004-08-18 04:24:20.93 Starting up database 'MyDB'.
...
2004-08-18 04:24:21.45 spid5 Starting up database 'tempdb'.
2004-08-18 04:24:21.48 spid13 Error: 823, Severity: 24, State: 2
2004-08-18 04:24:21.48 spid13 I/O error (torn page) detected during read at offset 0x00000000002000 in file 'D:\MSSQL\Data\distribution.MDF'..
2004-08-18 04:24:21.63 spid8 Error: 823, Severity: 24, State: 2
2004-08-18 04:24:21.63 spid8 I/O error (torn page) detected during read at offset 0x00000000b06000 in file 'D:\MSSQL\data\msdbdata.mdf'..
2004-08-18 04:24:21.68 spid8 Error: 3313, Severity: 21, State: 2
2004-08-18 04:24:21.68 spid8 Error while redoing logged operation in database 'msdb'. Error at log record ID (5538:145:3)..
2004-08-18 04:24:21.68 spid13 Error: 3313, Severity: 21, State: 2
2004-08-18 04:24:21.68 spid13 Error while redoing logged operation in database 'distribution'. Error at log record ID (845:180:4)..
2004-08-18 04:24:21.73 spid13 Error: 3414, Severity: 21, State: 1
2004-08-18 04:24:21.73 spid13 Database 'distribution' (database ID 9) could not recover. Contact Technical Support..
2004-08-18 04:24:21.73 spid8 Error: 3414, Severity: 21, State: 1
2004-08-18 04:24:21.73 spid8 Database 'msdb' (database ID 4) could not recover. Contact Technical Support..
2004-08-18 04:24:21.79 spid9 Error: 823, Severity: 24, State: 2
2004-08-18 04:24:21.79 spid9 I/O error (torn page) detected during read at offset 0x00000079f4c000 in file 'D:\MSSQL\data\MyDB_Data.MDF'..
2004-08-18 04:24:21.81 spid9 Error: 3313, Severity: 21, State: 2
2004-08-18 04:24:21.81 spid9 Error while redoing logged operation in database 'MyDB'. Error at log record ID (37496:16:323)..
2004-08-18 04:24:21.84 spid9 Error: 3414, Severity: 21, State: 1
2004-08-18 04:24:21.84 spid9 Database 'MyDB' (database ID 5) could not recover. Contact Technical Support..

2004-08-18 04:24:21.99 spid15 1 transactions rolled forward in database 'CLIENT_TEMP' (11).
2004-08-18 04:24:22.02 spid15 0 transactions rolled back in database 'CLIENT_TEMP' (11).
2004-08-18 04:24:22.07 spid15 Recovery is checkpointing database 'CLIENT_TEMP' (11)
2004-08-18 04:24:22.35 spid10 1 transactions rolled forward in database 'RADE_TEMP' (6).
2004-08-18 04:24:22.38 spid10 0 transactions rolled back in database 'RADE_TEMP' (6).
2004-08-18 04:24:22.43 spid10 Recovery is checkpointing database 'RADE_TEMP' (6)
2004-08-18 04:24:23.90 spid16 1 transactions rolled forward in database 'CLIENT_XFER' (12).
2004-08-18 04:24:23.93 spid16 0 transactions rolled back in database 'CLIENT_XFER' (12).
2004-08-18 04:24:23.93 spid12 1 transactions rolled forward in database 'CLIENT_DEV' (8).
2004-08-18 04:24:23.95 spid16 Recovery is checkpointing database 'CLIENT_XFER' (12)
2004-08-18 04:24:23.98 spid12 0 transactions rolled back in database 'CLIENT_DEV' (8).
2004-08-18 04:24:24.01 spid12 Recovery is checkpointing database 'CLIENT_DEV' (8)
2004-08-18 04:24:24.23 spid3 Recovery complete.
2004-08-18 04:24:24.23 spid3 SQL global counter collection task is created.
2004-08-18 04:24:24.27 spid3 Launched startup procedure 'sp_MSrepl_startup'
2004-08-18 04:24:24.31 spid3 Launched startup procedure 'sp_MScleanupmergepublisher'
2004-08-18 04:24:25.63 server SQL server listening on TCP, Shared Memory, Rpc.
2004-08-18 04:24:25.63 server SQL Server is ready for client connections
(last entry in Archive#1 log)
(No tidy shutdown)

SQL AGENT is clearly not running, so no hourly TRANS backups beyond this point

Current log starts with:

2004-08-18 07:20:06.64 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
...
2004-08-18 07:20:26.79 spid9 Starting up database 'MyDB'.
...
2004-08-18 07:20:26.93 spid5 Clearing tempdb database.
2004-08-18 07:20:27.06 spid13 Bypassing recovery for database 'distribution' because it is marked SUSPECT.
2004-08-18 07:20:27.18 spid9 Bypassing recovery for database 'MyDB' because it is marked SUSPECT.
2004-08-18 07:20:27.24 spid8 Bypassing recovery for database 'msdb' because it is marked SUSPECT.

2004-08-18 07:20:27.31 spid5 Starting up database 'tempdb'.
2004-08-18 07:20:29.67 spid3 Recovery complete.

I have backups for distribution, MyDB and msdb from 2004-08-18 02:00, shall I just restore them? or is the damage more widespread?

Thanks

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-18 : 07:46:23
I would try to change the mode out of suspect first, then run a DBCC CHECKDB on each database to see how big the damage is. We had this happen here when we were on the old hardware several times (don't ask).

If that doesn't work, restore only the user db and the msdb db. Then, run the DBCC CHECKDB on all databases again. You want to do this on the system databases as well btw. What kind of disk encosure are you running this SQL Server on? You need to make sure the enclosures themselves have a UPS on them.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-18 : 08:41:03
Thanks Derrick. The drives are a RAID within the computer box itself.

<fx:toddles off to DBCC>

<fx:he may be gone sometime - its TS into thir web box, and then from there TS into their office via VPN>

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-08-18 : 13:32:19
I ran a DBCC CHECKDB on all databases that were NOT suspect. They were fine.

I could NOT run sp_resetstatus on the suspect UserDB. The SProc ran fine, I restarted SQL, it was still marked suspect, running the SProc again said "Nothing to do, already flagged" or somesuch.

So I tried to restore the UserDB in EM (I know, I know, ...) That said that MSDB was corrupted so Abandon Ye All Hope Who Enter Here ...

So I did a RESTORE using QA. Even that seemed a little miserable doing a restore of UserDB whilst msdb was Suspect.

I restored from 2 days ago, just to be sure.

So I restored msdb and distribution too.

The SUSPECT flag went away.

I did a DBCC CHECKDB on those


They all came up with

"CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDB'."


It would probably have been better to restore MSDB first - the subsequent RESTORE of the other databases would have stored the "current backup status" in MSDB correctly, and probably make going-forwards more robust.

So after MSDB I re-restored the other suspect databases.

And after DBCC everything once more I did a FULL BACKUP on everything, and a TRANS BACKUP so that I was (hopefully) at Ground Zero

I also DISABLED the SQL AGENT service for the duration of this exercise - I didn't want another Power Cut causing a restart, and SQL AGENT to get activated, and then start running my Hourly Data Import tasks and everything else before I'd got it back to normal!

Does that sound about right?

Kristen
Go to Top of Page
   

- Advertisement -