March 19, 2013 at 7:16 pm
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.
March 19, 2013 at 7:29 pm
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.
March 20, 2013 at 1:50 am
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?
March 20, 2013 at 1:52 am
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
March 20, 2013 at 6:42 am
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.
March 20, 2013 at 7:33 am
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).
March 20, 2013 at 8:01 am
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?
March 20, 2013 at 12:23 pm
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
March 20, 2013 at 1:40 pm
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.
March 20, 2013 at 6:46 pm
I'm interested to know what the issue was. I've had a similar issue with a bad SAN firmware.
March 21, 2013 at 7:48 am
foxxo,
Not sure if I can give you enough info since I don't understand hardware very much myself. But, here goes.
We are in a virtual environment. With our Term Server, SQL Server, and Domain all on one box. We then have a NAS. Unfortunately our 'pipeline' to the NAS from all the servers is only one pipeline. We don't have a dedicated pipeline to just the SQL Server. I am being told that it is a bad design of our infrastructure.
Does this make sense? If you have other questions, I may be able to get the answers.
March 21, 2013 at 8:11 am
If it's a NAS, you need to make very sure that is supports SQL's IO requirements. SQL requires write order preservation and that there be no sector rewrites.
IO contention won't cause corruption, so the fact that it's a single route shouldn't be a problem. Something in that IO stack however is not behaving correctly if you have repeated corruption.
Oh, and SQL Server and the DC on the same machine is a bad setup.
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
March 21, 2013 at 9:29 am
GilaMonster,
They are on Virtual Machines on the same hardware. So, they are essentially on separate machines.
I will pass your last note along to my co-worker and my hardware tech.
April 9, 2013 at 12:48 pm
If you run DBCC CHECKDB and this command do not work properly then you can try third party tool to repair corrupt .mdf file. I recommend you SQL recovery software as I have also faced that type of problem. But with the help of this application I recovered entire database safe condition. So you can also use this application and save your precious data. I you have any issue regarding this recovery process you can visit this link: http://www.systoolsgroup.com/sql-recovery.html
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply