Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Upgraded from SQL2000 to SQL2008R2 - Now have corruption Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 7:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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.
Post #1432970
Posted Tuesday, March 19, 2013 7:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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.
Post #1432971
Posted Wednesday, March 20, 2013 1:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
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?
Post #1433038
Posted Wednesday, March 20, 2013 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #1433040
Posted Wednesday, March 20, 2013 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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.

Post #1433178
Posted Wednesday, March 20, 2013 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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).
Post #1433201
Posted Wednesday, March 20, 2013 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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?
Post #1433231
Posted Wednesday, March 20, 2013 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #1433405
Posted Wednesday, March 20, 2013 1:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:22 AM
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.
Post #1433457
Posted Wednesday, March 20, 2013 6:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
I'm interested to know what the issue was. I've had a similar issue with a bad SAN firmware.
Post #1433562
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse