options for corrupt dbs.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

alanccw

Yes, with CheckDB, you need to switch to single user mode and then perform the recovery, of course, before doing that, you need to backup it manually. See http://www.datanumen.com/sql-recovery/articles/dbcc-checkdb.htm

ASKER

>>Did you reset the DB status before doing that?
do you recommend sp_resetstatus before REPAIR_ALLOW_DATA_LOSS?

BOL says "... all problems should be resolved before running this procedure"..
http://msdn.microsoft.com/en-us/library/ms188424(v=sql.90).aspx

how can I 'resolve all problems found in error log or DBCC CHECKDB" before running REPAIR_ALLOW_DATA_LOSS (& sp_resetstatus)?

pl suggest.

ASKER

>>Yes, with CheckDB, you need to switch to single user mode and then perform the recovery, of course, before doing that, you need to backup it manually.

by "perform the recovery", you are referring to REPAIR_ALLOW_DATA_LOSS, right?

>>before doing that, you need to backup it manually.
right now I am not able to do any backup on the dababase since it is in SUSPECT mode.

Yes, ideally, I need a backup now, since REPAIR_ALLOW_DATA_LOSS may allow data loss, but I am at a loss, since it won't allow me to do a backup in emergency (suspect) state.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

>>EXEC sp_resetstatus <DatabaseName> should be the first thing to do.
OK. I did not do that before putting DB on emergency. How do you recommend me to run it? Just run it now? or restart machine and then run it?

What exactly does EXEC sp_resetstatus do? (other than taking it off from SUSPECT mode?)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Scott, not able to take backup now.. (because of SUSPECT/Emergency mode)

I will move the error log away to safe place... but the first error happened 3 days ago:

2014-09-07 05:34:13.030      spid72      A time-out occurred while waiting for buffer latch -- type 2, bp 0359F010, page 1:652536, stat 0xc0010b, database id: 9, allocation unit Id: 72057609715712000, task 0x0084B5B8 : 0, waittime 300, flags 0x4001a, owning task 0x008D92E8. Not continuing to wait.
2014-09-07 05:34:13.380      spid72      Using 'dbghelp.dll' version '4.0.5'
2014-09-07 05:34:14.300      spid72      **Dump thread - spid = 72, PSS = 0x2338B578, EC = 0x2338B580
2014-09-07 05:34:14.300      spid72      ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0122.txt
2014-09-07 05:34:14.300      spid72      * *******************************************************************************
2014-09-07 05:34:14.300      spid72      *
2014-09-07 05:34:14.300      spid72      * BEGIN STACK DUMP:
2014-09-07 05:34:14.300      spid72      * 09/07/14 05:34:14 spid 72
2014-09-07 05:34:14.300      spid72      *
2014-09-07 05:34:14.300      spid72      * Latch timeout
2014-09-07 05:34:14.300      spid72      *
2014-09-07 05:34:14.310      spid72      * Input Buffer 108 bytes -
2014-09-07 05:34:14.310      spid72      * 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
2014-09-07 05:34:14.310      spid72      * C u s t o m 01 00 00 00 10 00 43 00 75 00 73 00 74 00 6f 00 6d 00
2014-09-07 05:34:14.310      spid72      * e r I n f o _ G e 65 00 72 00 49 00 6e 00 66 00 6f 00 5f 00 47 00 65 00
2014-09-07 05:34:14.310      spid72      * t & D § 74 00 00 00 00 00 26 04 04 44 09 00 00 00 00 a7 04 00
2014-09-07 05:34:14.310      spid72      * Ð 4 CACF & ß 09 04 d0 00 34 04 00 43 41 43 46 00 00 26 04 04 df 07
2014-09-07 05:34:14.310      spid72      * § Ð 4 CACF 00 00 00 00 a7 08 00 09 04 d0 00 34 04 00 43 41 43 46
2014-09-07 05:34:14.310      spid72      *
2014-09-07 05:34:14.310      spid72      * *******************************************************************************
2014-09-07 05:34:14.310      spid72      * -------------------------------------------------------------------------------
2014-09-07 05:34:14.310      spid72      * Short Stack Dump
2014-09-07 05:34:14.510      spid72      Stack Signature for the dump is 0xA4065D10
2014-09-07 05:34:19.030      spid72      External dump process return code 0x20002001. The error information has been submitted to Watson error reporting.

ASKER

Vitor,
when I ran
EXEC sp_resetstatus
it says
The suspect flag on the database is already reset.

so perhaps all that sp_resetstatus does is to put the DB on emergency mode, so we can try to run CHECKDB again on the db. (now it (still) says emergency in sys.databases for this db, after running sp_resetstatus)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

When you changed the DB to Emergency mode, the engine took it as a reset for sure.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

>> logically "zap" db status on SQL 2005
what exactly does that mean?

ASKER

in terms of checking if the existing last backup is good and void of corruption, is

RESTORE VERIFYONLY FROM DISK =''
good enough? would you recommend anything else also in addition to confirm the backups?

right now, the above query returns back
The backup set on file 1 is valid.
for the FULL backup file and all TG's. is that enough to confirm?

Yes it is.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

OK, thanks.

Now, I have RESTORED from these files, and CHECKDB gives no error (I am doing this on another server). Now I plan to take this database (with a backup file) to the original server. then I will restore there and checkDB again. Any other thoughts/ideas I should remember in this process?

ASKER

the network team says 'drives, controllers etc' are all OK.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.