| Author |
Topic |
|
dzach
Starting Member
38 Posts |
 Posted - 2006-07-27 : 11:06:45
|
| 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 1Object 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 1Table 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 1Table 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 1Table 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 1Table 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 1Table 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 1Suggestions on how to troubleshoot/fix?Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 11:17:34
|
| 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 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-27 : 11:18:14
|
| 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. |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-07-27 : 11:52:41
|
quote:
Originally posted by KristenDo 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 foundquote:
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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 12:08:13
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 12:09:28
|
| 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 |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-07-27 : 12:48:12
|
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 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-07-27 : 14:59:17
|
| 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?ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 15:14:03
|
"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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 15:22:29
|
| "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 |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-07-28 : 10:31:09
|
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 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-07-29 : 21:21:26
|
| Can you do the following and post the results please?dbcc traceon (3604)godbcc page (databasename, 1,1868079,3)goThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-08-01 : 08:38:11
|
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 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-01 : 09:00:56
|
| 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 athttp://www.nigelrivett.net/SQLAdmin/PageStructure.htmlor 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-02 : 04:08:36
|
| Please let it be credit card numbers |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-08-02 : 08:25:42
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-02 : 08:31:20
|
| "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 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-08-02 : 10:35:20
|
| 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.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-02 : 11:08:35
|
| 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 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-08-02 : 11:13:08
|
| Yup - that would work.Paul RandalLead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
CheckDB Errors