Solved

options for corrupt dbs.

Posted on 2014-09-10
21
186 Views
Last Modified: 2015-09-26
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.
0
Comment
Question by:25112
  • 9
  • 5
  • 5
  • +1
21 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 188 total points
ID: 40314520
Did you reset the DB status before doing that?

EXEC sp_resetstatus <DatabaseName>
0
 
LVL 3

Expert Comment

by:alanccw
ID: 40314545
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
0
 
LVL 5

Author Comment

by:25112
ID: 40314557
>>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.
0
 
LVL 5

Author Comment

by:25112
ID: 40314565
>>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.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 188 total points
ID: 40314576
EXEC sp_resetstatus <DatabaseName>  should be the first thing to do. Even before put DB in emergency mode.
0
 
LVL 5

Author Comment

by:25112
ID: 40314628
>>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?)
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 312 total points
ID: 40314639
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.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 188 total points
ID: 40314645
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.
0
 
LVL 5

Author Comment

by:25112
ID: 40314676
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.
0
 
LVL 5

Author Comment

by:25112
ID: 40314682
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)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 312 total points
ID: 40314691
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40314695
When you changed the DB to Emergency mode, the engine took it as a reset for sure.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 312 total points
ID: 40314711
Rats, was thinking you could still logically "zap" db status on SQL 2005, but you can't.
0
 
LVL 5

Author Comment

by:25112
ID: 40314763
>> logically "zap" db status on SQL 2005
what exactly does that mean?
0
 
LVL 5

Author Comment

by:25112
ID: 40314779
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?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40314802
Yes it is.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 312 total points
ID: 40314810
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".
0
 
LVL 5

Author Comment

by:25112
ID: 40314860
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?
0
 
LVL 5

Author Comment

by:25112
ID: 40315225
the network team says 'drives, controllers etc' are all OK.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 312 total points
ID: 40315237
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Group by and order by clause 28 36
Sql query 34 20
When to use an Aggregate Function. 18 37
SQL JOIN + SUBQUERY? 3 14
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now