SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 CheckDB Errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dzach
Starting Member

38 Posts

Posted - 07/27/2006 :  11:06:45  Show Profile  Reply with Quote
Hi,

Ran DBCC CHECKDB on my database and it's returning the following:

There are 460 rows in 13 pages for object 'KPW'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1977058079, index ID 0: Page (1:1868079) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1977058079, index ID 0, page (1:1868079), row 76. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 173 and 193.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1977058079, index ID 0, page (1:1868079), row 76. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 16374 and 33.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1977058079. The text, ntext, or image node at page (1:1867931), slot 8, text ID 205274873856 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1977058079. The text, ntext, or image node at page (1:1867932), slot 0, text ID 205275004928 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1977058079. The text, ntext, or image node at page (1:1867932), slot 2, text ID 205275136000 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1

Suggestions on how to troubleshoot/fix?

Thanks!

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  11:17:34  Show Profile  Reply with Quote
Whilst waiting for a higher authority!

Do you have backups? If so just Full backup, or including transaction backups? How recent?

How long since you previously ran DBCC CHECKDB (i.e. what period has the corruption occurred within)?

Is there anything in the EVENT log - e.g. that suggests a hardware fault?

Does DBCC say what level of REPAIR is required (i.e. WITH or WITHOUT data loss)?

I sugest that you STOP SQL Server service and COPY the MDF and LDF - so that you have a working copy that you could start-agin from - before doing anything drastic. Do NOT detach the database in case it will not reattach.

Kristen
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  11:18:14  Show Profile  Visit nr's Homepage  Reply with Quote
I would restore from the latest backup.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

dzach
Starting Member

38 Posts

Posted - 07/27/2006 :  11:52:41  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Do you have backups? If so just Full backup, or including transaction backups? How recent?

Yes, we have backups, but unforunately, we believe they're possibly corrupt as well.

quote:

How long since you previously ran DBCC CHECKDB (i.e. what period has the corruption occurred within)?


It's been over three months for sure (and maybe longer) since DBCC CHECKDB was ran. Part of the problem is that no one was monitoring the database.

quote:

Is there anything in the EVENT log - e.g. that suggests a hardware fault?


There's nothing obvious in the event logs that point to hardware issues.


quote:

Does DBCC say what level of REPAIR is required (i.e. WITH or WITHOUT data loss)?



It says: repair_allow_data_loss is the minimum repair level for the errors found

quote:

I sugest that you STOP SQL Server service and COPY the MDF and LDF - so that you have a working copy that you could start-agin from - before doing anything drastic. Do NOT detach the database in case it will not reattach.




Will do.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  12:08:13  Show Profile  Reply with Quote
If you have Transaction Backups (going back far enough) it may well be that they are NOT corrupted (because they are backing up the transactions, NOT the state of the main database file), so you may be able to recover a known-good Full backup, and then each Transaction Backup in turn.

I recommend that you copy the "copy" of the MDF and LDF files to a different machine, re-attach it there, and try the repair_allow_data_loss. You could then try comparing the two databases to see what is missing, and decide if you could reconstitute the missing data.

(RedGate's Compare tool would help here, and I believe they have a trail period)

(For the avoidance of doubt please make sure that your original copy cannot be accidentally used to connect to. Perhaps set the file to readonly, and back it up).

You should prevent anyone accessing the original database - further data changes may make it worse and reduce the possibility of recovery - but I'm not sure I would set it to SysAdmin, unless there is not other way to keep everyone out, just in case that change alone mucks up the corruption further.

Beyond that "rescue" is probably going to be by exporting all the "good" data, and then reconstituting any missing stuff. I should send out for some Pizza now!

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  12:09:28  Show Profile  Reply with Quote
P.S. If not already done I recommend running DBCC CHECKDB on all other DBs on that server (i.e. including Master and MSDB, but NOT on TempDB) to check if there is damage elsewhere)

Kristen
Go to Top of Page

dzach
Starting Member

38 Posts

Posted - 07/27/2006 :  12:48:12  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

If you have Transaction Backups (going back far enough) it may well be that they are NOT corrupted (because they are backing up the transactions, NOT the state of the main database file), so you may be able to recover a known-good Full backup, and then each Transaction Backup in turn.


When I look in the SQL Server logs in Enterprise Manager, I don't see any Transaction Backups being performed. This is a bad thing I assume.

quote:

I recommend that you copy the "copy" of the MDF and LDF files to a different machine, re-attach it there, and try the repair_allow_data_loss.

I'm not sure what you mean by re-attach to a different machine. Do you mean a different SQL server machine?

quote:

You should prevent anyone accessing the original database - further data changes may make it worse and reduce the possibility of recovery...

Unfortunately, that's not possible, given the kind of environment I work in.

quote:

Beyond that "rescue" is probably going to be by exporting all the "good" data, and then reconstituting any missing stuff. I should send out for some Pizza now!


It's gonna take more than one pizza I'm afraid!

Per your suggestion, I did run dbcc checkdb against MASTER and MSDB and there were on errors on either database.

Dale
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 07/27/2006 :  14:59:17  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Sorry for jumping in late on this one.

Are you saying that there are errors on master and msdb as well?

Can you check you system event logs for evidence of h/w problems? What about IO errors in the SQL Server error log?

The first error is a badly corrupt row - is that the only error?

Can you run full hardware diagnostics on your box and IO subsystem? Are all your drivers and firmware uptodate?

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  15:14:03  Show Profile  Reply with Quote
"Are you saying that there are errors on master and msdb as well?"

I took that to be a typo Paul:

"Per your suggestion, I did run dbcc checkdb against MASTER and MSDB and there were on no errors on either database."

but would be good to have confirmation of course.

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  15:22:29  Show Profile  Reply with Quote
"When I look in the SQL Server logs in Enterprise Manager, I don't see any Transaction Backups being performed. This is a bad thing I assume."

Reduces your recover options, if your full backups are damaged. However, if the corruption has been around for a while they may have been useless anyway.

Please check that your database recovery model IS set to Simple, and not to Full.

IF it is set to FULL then there is a chance that you can make a Transaction Backup now, and use it to roll-forwards from a suitably old Full backup. (Best to do a test of this scenario on a different machine!!)

"I'm not sure what you mean by re-attach to a different machine. Do you mean a different SQL server machine?"

Yes. If you don't have another server you could install MSDE on your PC and use that as a test-bed (assuming the DB is less than the 2GB limit of MSDE)

But obviously take Paul's advice, I don't want to get in the way!

Kristen
Go to Top of Page

dzach
Starting Member

38 Posts

Posted - 07/28/2006 :  10:31:09  Show Profile  Reply with Quote
quote:

Are you saying that there are errors on master and msdb as well?


There are NO errors on the MASTER and MSDB databases.

quote:
Can you check you system event logs for evidence of h/w problems? What about IO errors in the SQL Server error log?


There's no indication of hardware errors in the event logs.

quote:

The first error is a badly corrupt row - is that the only error?


I don't see any reference to a badly corrupt row, but then, I'm not sure what I'm looking at.

quote:
Can you run full hardware diagnostics on your box and IO subsystem?

I don't have access to the server in our environment.

quote:
Are all your drivers and firmware uptodate?

Yes.

Thanks

Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 07/29/2006 :  21:21:26  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Can you do the following and post the results please?

dbcc traceon (3604)
go
dbcc page (databasename, 1,1868079,3)
go

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

dzach
Starting Member

38 Posts

Posted - 08/01/2006 :  08:38:11  Show Profile  Reply with Quote
quote:
Originally posted by paulrandal

Can you do the following and post the results please?



Hi Paul,

What are these commands going to do?

Dale
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/01/2006 :  09:00:56  Show Profile  Visit nr's Homepage  Reply with Quote
the trace command redirects the dbcc page output so you can see it.
the dbcc page displays the data as stored in the database.
I assume Paul has got the page reference from the checkdb output.

Have a look at
http://www.nigelrivett.net/SQLAdmin/PageStructure.html
or get a copy of Inside SQL Server.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2006 :  04:08:36  Show Profile  Reply with Quote
Please let it be credit card numbers
Go to Top of Page

dzach
Starting Member

38 Posts

Posted - 08/02/2006 :  08:25:42  Show Profile  Reply with Quote
I ended up running the ALLOW_DATA_LOSS repair and it worked!! Thanks for all the help everyone. This has been the most helpful forum I've visited yet. Bar none.

Do you guys/gals answer ASP questions as well?

Dale
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2006 :  08:31:20  Show Profile  Reply with Quote
"I ended up running the ALLOW_DATA_LOSS repair and it worked"

Do you know what data DBCC has deleted, so that you can recreate it? (May just be indexes etc., in which case recreating the effected index would solve that problem, but I expect if that was the case Paul would have recommended that as a solution)

Kristen
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/02/2006 :  10:35:20  Show Profile  Visit paulrandal's Homepage  Reply with Quote
No Kristen, from the initial set of CHECKDB messages, its a SQL 2000 system (the messages have changed in 2005 to include an allocation unit ID as well) and the index ID is 0, which means the damaged row is in a heap or clustered index data page. Also, in 2000, non-clustered indexes can't have references to off-row LOB data - only with INCLUDEd columns in 2005 is this possible.

Repair is one way of going about it, but it will have deleted that row so you've lost data. A far better option is to use a backup - I recommend you come up with a comprehensive backup strategy to use in case this happens again in future.

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2006 :  11:08:35  Show Profile  Reply with Quote
Cheers Paul.

So it might be worth restoring a before-repair backup to a temporary database and comparing the PKs to identify which record(s) have been zapped, so they can be recreated manually.

Kristen
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/02/2006 :  11:13:08  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Yup - that would work.

Paul Randal
Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000