options for corrupt dbs.

when a database says it is SUSPECT, i put it on EMERGENCY mode and then ran CHECKDB.. but it gave lot of consistency errrors...

so is my only option now to do a REPAIR_ALLOW_DATA_LOSS on single user mode? no other option?
(not sure if the backups are good and void of corruption- someone is going to test that also).

the server is SQL 2005.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you reset the DB status before doing that?

EXEC sp_resetstatus <DatabaseName>
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
25112Author Commented:
>>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"..

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

pl suggest.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

25112Author Commented:
>>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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
EXEC sp_resetstatus <DatabaseName>  should be the first thing to do. Even before put DB in emergency mode.
25112Author Commented:
>>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?)
Scott PletcherSenior DBACommented:
No, *first* take a backup, so you are never worse off than you are now.  After recovering the main db, you can restore the backup and try to export individually any tables that lost data.

*Next*, you should immediately copy the current SQL error logs to another location.  Then review them to see if you can find the *original* corruption.  Only the original/first error is more fully detailed in the error logs.  Because those logs cycle off, you need to capture them NOW.

Then you can do the other recovery steps.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Takes the DB from Suspect mode so you can do any operation with the database.
Try it now. Won't do anything worst that you already have for sure.
25112Author Commented:
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.
25112Author Commented:
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)
Scott PletcherSenior DBACommented:
You need to have the I/O subsystem -- drives, controllers, etc. -- checked for errors.  If that's bad, it needs corrected first, because SQL can't anticipate drive errors.

After you "zap" the db back, by forcibly resetting the status, the first thing you want to do is try to do a backup.  If that doesn't work, then you know the corruption is very severe :( .

Obviously keep any existing historical backups you have from before the corruption, or the first one after the corruption.  That version may be less damaged and allow more data to be copied out of it.

As to the existing db, yes, ultimately you simply have to run a "REPAIR_ALLOW_DATA_LOSS" and see if (1) it works and (2) how much data is lost / unrecoverable.
Vitor MontalvãoMSSQL Senior EngineerCommented:
When you changed the DB to Emergency mode, the engine took it as a reset for sure.
Scott PletcherSenior DBACommented:
Rats, was thinking you could still logically "zap" db status on SQL 2005, but you can't.
25112Author Commented:
>> logically "zap" db status on SQL 2005
what exactly does that mean?
25112Author Commented:
in terms of checking if the existing last backup is good and void of corruption, is

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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes it is.
Scott PletcherSenior DBACommented:
RESTORE VERIFYONLY only checks to see that the backup media is valid and readable, NOT that the data within the db is good.  That is, it says "the data can be read" NOT "once restored the db will be without error".
25112Author Commented:
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?
25112Author Commented:
the network team says 'drives, controllers etc' are all OK.
Scott PletcherSenior DBACommented:
That's good in the sense that other dbs should be OK.  That's bad in the sense then you don't know what caused this db to be corrupted.

Verify that the db has the correct level of page verification set; if not, you need to properly set it as soon as the db is recovered:

SELECT page_verify_option_desc
FROM sys.databases
WHERE name = '<your_db_name>'

You want to see "CHECKSUM", nothing else.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.