SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgraded from SQL2000 to SQL2008R2 - Now have corruption


Upgraded from SQL2000 to SQL2008R2 - Now have corruption

Author
Message
wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
We have corruption in our database since we upgraded our SQL in the last 3 weeks. I have run DBCC CHECKDB in SSMS and have returned error messages. I have seen this article http://www.sqlservercentral.com/articles/Corruption/65804/ on this website and am just wondering what approach I should take in repairing/fixing/etc.

I was told by someone at the software company that our database uses I have three choices right now and I would like to at least try to fix this ourselves. My three choices right now are:
1. Run DBCC CHECKBD and allow data loss.
2. Contact Microsoft to see if they can help me. If MS can't help they will send me to a data recovery company.
3. I can use the software companies tech support (who may still not be able to help me).

Hopefully one of you can point me in the correct direction. I really want to get this fixed before we are unable to process at all.
wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
I just ran the following:

DBCC CHECKDB (TestingCHECKDB) WITH NO_INFOMSGS, ALL_ERRORMSGS

And got the following error(s):

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 2254
Do not use the ALLOW_DATALOSS option unless it's your absolute last resort.

Do you have any backups that can be restored?

What error messages are in the SQL error log at the time you ran DBCC CHECKDB?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86785 Visits: 45254
Most likely what happened here is you had corruption in the 2000 database, checkDB wasn't as good on 2000 and so missed it, after upgrade it was picked up.

Got a backup of the SQL 2000 database prior to upgrade?

btw, if CheckDB's failing like that, you're not going to be able to run a repair at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
foxxo (3/20/2013)
Do not use the ALLOW_DATALOSS option unless it's your absolute last resort.

Do you have any backups that can be restored?

What error messages are in the SQL error log at the time you ran DBCC CHECKDB?


We are only using the ALLOW_DATALOSS on a testing database right now to see what happened. I have backups.
wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
GilaMonster (3/20/2013)
Most likely what happened here is you had corruption in the 2000 database, checkDB wasn't as good on 2000 and so missed it, after upgrade it was picked up.

Got a backup of the SQL 2000 database prior to upgrade?

btw, if CheckDB's failing like that, you're not going to be able to run a repair at all.


We reran the CheckDB's on a "clean" copy of the database. After experiencing errors my co-worker ran the CheckDB allowing dataloss. I will post the errors that we got. As you will be able to see, everything repaired. My issue now is that this is just a band-aid. We need to find the underlying root cause.

DBCC results for 'audit_trail'.
Repair: The Clustered index successfully rebuilt for the object "dbo.audit_trail" in database "CommerceCenter".
Repair: The page (1:844857) has been deallocated from object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data).
Repair: The page (1:844858) has been deallocated from object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data).
Repair: The page (1:844996) has been deallocated from object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data).
Repair: The page (1:859074) has been deallocated from object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data).
Repair: The Nonclustered index successfully rebuilt for the object "dbo.audit_trail, idx_audit_trail_criteria" in database "CommerceCenter".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.audit_trail, idx_audit_trail_key2_criteria" in database "CommerceCenter".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data): Page (1:844857) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:844857) was not seen in the scan although its parent (1:840210) and previous (1:844856) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844857), row 43. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28672 and 140.
The error has been repaired.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844857), row 43. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28672 and 140.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data): Page (1:844858) could not be processed. See other errors for details.
The error has been repaired.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Index node page (1:840210), slot 213 refers to child page (1:844858) and previous child (1:844857), but they were not encountered.
The error has been repaired.
Msg 8944, Level 16, State 15, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844858), row 26. Test (nVarCols && (hdr->r_tagA & RecConst::VARIABLE_COLUMNS)) failed. Values are 0 and 32.
The error has been repaired.
Msg 8944, Level 16, State 15, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844858), row 26. Test (nVarCols && (hdr->r_tagA & RecConst::VARIABLE_COLUMNS)) failed. Values are 0 and 32.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:844859) is missing a reference from previous page (1:844858). Possible chain linkage problem.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data): Page (1:844996) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:844996) was not seen in the scan although its parent (1:840210) and previous (1:844995) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844996), row 9. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28524 and 153.
The error has been repaired.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:844996), row 9. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28524 and 153.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:844997) is missing a reference from previous page (1:844996). Possible chain linkage problem.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data): Page (1:859074) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:859074) was not seen in the scan although its parent (1:840243) and previous (1:859073) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:859074), row 26. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 0 and 88.
The error has been repaired.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data), page (1:859074), row 26. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 0 and 88.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 1, partition ID 72057594907000832, alloc unit ID 72057594929676288 (type In-row data). Page (1:859075) is missing a reference from previous page (1:859074). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 2 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 967830660, index ID 3 will be rebuilt.
The error has been repaired.
There are 15377664 rows in 330401 pages for object "audit_trail".
CHECKDB found 0 allocation errors and 19 consistency errors in table 'audit_trail' (object ID 967830660).
CHECKDB fixed 0 allocation errors and 19 consistency errors in table 'audit_trail' (object ID 967830660).

Repair: The Clustered index successfully rebuilt for the object "dbo.apinv_line" in database "CommerceCenter".
Repair: The page (1:792086) has been deallocated from object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data).
Repair: The page (1:792090) has been deallocated from object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data).
Repair: The Nonclustered index successfully rebuilt for the object "dbo.apinv_line, idx_apinv_line_voucher" in database "CommerceCenter".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.apinv_line, idx_apinv_line_totals" in database "CommerceCenter".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.apinv_line, idx_apinv_line_parent_apinv_line_uid" in database "CommerceCenter".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data): Page (1:792086) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data). Page (1:792086) was not seen in the scan although its parent (1:772246) and previous (1:792085) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data), page (1:792086), row 12. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2949 and 222.
The error has been repaired.
Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data), page (1:792086), row 12. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2949 and 222.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data). Page (1:792087) is missing a reference from previous page (1:792086). Possible chain linkage problem.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data): Page (1:792090) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data). Page (1:792090) was not seen in the scan although its parent (1:772246) and previous (1:792089) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data), page (1:792090), row 9. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 32767 and 217.
The error has been repaired.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data), page (1:792090), row 9. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 32767 and 217.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 1, partition ID 72057594905231360, alloc unit ID 72057594927906816 (type In-row data). Page (1:792091) is missing a reference from previous page (1:792090). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 2 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 3 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1680777095, index ID 4 will be rebuilt.
The error has been repaired.
There are 359166 rows in 10766 pages for object "apinv_line".
CHECKDB found 0 allocation errors and 10 consistency errors in table 'apinv_line' (object ID 1680777095).
CHECKDB fixed 0 allocation errors and 10 consistency errors in table 'apinv_line' (object ID 1680777095).
wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
GilaMonster (3/20/2013)
Most likely what happened here is you had corruption in the 2000 database, checkDB wasn't as good on 2000 and so missed it, after upgrade it was picked up.

Got a backup of the SQL 2000 database prior to upgrade?


Here is a little background:

We upgraded from SQL 2000 (Win2003 Server) to SQL 2008R2 (Win2012 Server) on March 2, 2013. We still have a copy of that database. We still have the old server powered up and able to retrieve whatever we want. We now have ESET File Security for Windows Server on the new server (it looks as if there was NOT an anti-virus on the old server). We have done a repair on the database twice now.

We discovered problems the week of March 4th when one of my users was unable to reconcile payments in the software that uses the database. Here is the error that we got when looking at payments tables:

An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

We then ran the DBCC CHECKDB and discovered issues with audit trails and ap invoice lines. We don't care about data loss in the audit trails. The ap invoice lines are not necessarily business critical to lose, so we made the decision to allow data loss.

After repairing, my user was able to reconcile payments. However, our check register won't work now. I am able to retrieve the data by running queries on different tables.

My question for you: Is there a way to fix the database so that we don't have to continue to repair and allow data loss? Or, could you give me a plan of action that I should take?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86785 Visits: 45254
If you're getting recurring corruption (you repair, corruption comes back, you repair, etc), your IO subsystem is faulty, check logs, update drivers, etc. SQL is not in the habit of corrupting its own databases and if checkDB repairs successfully, then it's repaired and there's no more corruption.

p.s. Repair is the last resort when fixing corruption, for when you don't have a backup. Your recommended approach is to restore the last good backup and transaction log backups.

Take a look at this article. http://www.sqlservercentral.com/articles/65804/
Don't just run repair

It may be tempting to just run CheckDB with one of the repair options (typically allow data loss) and believe that it will make everything better. In many cases running repair is not the recommended fix. It is not guaranteed to fix all errors and it may result in unacceptable data loss.

Repair is, in most cases, the last resort for fixing corruption. It should be done only when none of the alternatives are possible, not done as the first thing tried.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wendi
wendi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 15
GilaMonster.

We have found the underlying root cause (I think). Looks to be hardware related. Thanks for your replies. We are actually in a good position to recover. We do have tons of backups and transaction logs. We just have to go down the road of figuring out what the hardware issue is, restoring the backups/trans logs, and continuing down the road of business as usual.
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 2254
I'm interested to know what the issue was. I've had a similar issue with a bad SAN firmware.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search