May 11, 2011 at 7:34 am
I got the error 'SQL Server detected a logical consistency-based I/O error'
So I ran -
dbcc checkdb with data_purity
and got -
Table error: Object ID 101575400, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594197901312 (type In-row data), page (1:3296657). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
There are 20693586 rows in 1149644 pages for object "ADB_OPEN_YEAR".
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ADB_OPEN_YEAR' (object ID 101575400).
Now what can I do? believe it or not there is no backup of this DB!! god please help me!
May 11, 2011 at 7:37 am
Please run this
DBCC CHECKDB('db-name') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS
We can't provide a plan for the solution before we have all the errors.
May 11, 2011 at 7:43 am
Ninja's_RGR'us (5/11/2011)
DBCC CHECKDB('db-name') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS
Seconded, though personally I prefer the output without TableResults.
No backup? Someone's not doing their job...
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
May 11, 2011 at 7:55 am
GilaMonster (5/11/2011)
Ninja's_RGR'us (5/11/2011)
DBCC CHECKDB('db-name') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGSSeconded, though personally I prefer the output without TableResults.
No backup? Someone's not doing their job...
Ha sorry, I just copied that from my daily job... which logs what happens so I don't have to rerun it :w00t:.
May 11, 2011 at 7:58 am
No backup? Someone's not doing their job...
--------------------------------------------------------------------------------
I know...I know..not setup by me tho..the DBA before me skipped this Db for some odd reason. But that's an excuse I should have caught it.
the results of 'DBCC CHECKDB('lpb_greatplainsutils') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS' are:
Object ID 101575400, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594197901312 (type In-row data): Page (1:3296657) could not be processed. See other errors for details.
Table error: Object ID 101575400, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594197901312 (type In-row data), page (1:3296657). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ADB_OPEN_YEAR' (object ID 101575400).
Repair level - repair allow dataloss
ErrorLevelStateMessageTextRepairLevelStatusDbIdObjectIdIndexIdPartitionIdAllocUnitIdFilePageSlotRefFileRefPageRefSlotAllocation
8928161Object ID 101575400, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594197901312 (type In-row data): Page (1:3296657) could not be processed. See other errors for details.repair_allow_data_loss042101575400072057594041008128720575941979013121329665701329665601
89391698Table error: Object ID 101575400, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594197901312 (type In-row data), page (1:3296657). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.repair_allow_data_loss042101575400072057594041008128720575941979013121329665700001
8990101CHECKDB found 0 allocation errors and 2 consistency errors in table 'ADB_OPEN_YEAR' (object ID 101575400).NULL0421015754000000000001
8989101CHECKDB found 0 allocation errors and 2 consistency errors in database 'LPB_GreatPlainsUtils'.NULL0421015754000000000001
May 11, 2011 at 8:07 am
Take the DB into single user mode and run the following.
DBCC CheckDB('lpb_greatplainsutils', REPAIR_ALLOW_DATA_LOSS)
You will lose one page of data in the table ADB_OPEN_YEAR. Since it's a heap, not a cluster it's near-impossible to tell what is on that page (since heaps have no order of data)
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
May 11, 2011 at 8:10 am
GilaMonster (5/11/2011)
Take the DB into single user mode and run the following.DBCC CheckDB('lpb_greatplainsutils', REPAIR_ALLOW_DATA_LOSS)
You will lose one page of data in the table ADB_OPEN_YEAR. Since it's a heap, not a cluster it's near-impossible to tell what is on that page (since heaps have no order of data)
And I'd take 2 minutes to make the backup job and run it before the dataloss...
May 11, 2011 at 8:13 am
Then, once you've repaired, get this database into a backup plan of some form. Go and look around your environment for other databases that aren't been backed up. If you missed one, there's likely more.
Get a regular integrity check job running on all databases, preferably before the backup runs. I'm allergic to backing up corrupt DBs.
Finally, do some root cause analysis. SQL doesn't corrupt its own DBs, something outside of SQL caused this, most likely an IO subsystem error of some form.
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
May 11, 2011 at 9:06 am
Jsut re-ran dbcc checkdb
everything looks good now...
thanks you guys (and girls) rock...
May 11, 2011 at 9:11 am
krypto69 (5/11/2011)
Jsut re-ran dbcc checkdbeverything looks good now...
thanks you guys (and girls) rock...
You're not done. You still need to run checkdb pre backup. Stop the backups / delete backup jobs to run when there's a problem and send e-mails when there are issues.
Also find the root cause as gail said or you'll be right back here in a couple weeks.
May 11, 2011 at 9:15 am
As this is related to Great Plains, your companies accounting software, it would be in your best interest as their DBA to get this database in a full backup routine (will transaction logs) as soon as possible.
Should you need further information on the lost information, you should contact your Great Plains VAR.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
May 11, 2011 at 9:19 am
Hey Gail, if there had been a clustered index on that table. Could we have guessed at the lost data?.. or done something else?
May 11, 2011 at 9:24 am
Ninja's_RGR'us (5/11/2011)
Hey Gail, if there had been a clustered index on that table. Could we have guessed at the lost data?
Partially, yes. Would involve identifying the previous and next pages in the clustered index (remember, ordered logically by the clustering key) and then using DBCC page to read them. What that will give you is the clustered index keys that are on the damaged page. Maybe useful, maybe not. Depends what the cluster is.
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
May 11, 2011 at 9:30 am
Might have helped here. I'm building a dw so maybe a reverse engineer would have been possible in that scenario.
So bottom line, why is there no CI on that table? I can count on 1-2 fingers the times where it made sens to not have those in place!
May 11, 2011 at 9:32 am
Ninja's_RGR'us (5/11/2011)
So bottom line, why is there no CI on that table? I can count on 1-2 fingers the times where it made sens to not have those in place!
Vendor app (Great Plains). Probably done for compatibility across platforms
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply