SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
 
        
Home       Members    Calendar    Who's On


««123»»

Data Consistency Error Expand / Collapse
Author
Message
Posted Wednesday, July 01, 2009 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 14,524, Visits: 10,413
ps (7/1/2009)
Am still wondering why we didnt lost any data from one of the damaged pages that belonged to the other table.


Without seeing the output of checkDB for that database, no idea.



Gail Shaw

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

Post #745318
Posted Wednesday, July 01, 2009 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:399191). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:399192). The PageId in the page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 0: Page (1:399192) could not be processed. See other errors for details.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 1. Page (1:36631) is missing a reference from previous page (1:399192). Possible chain linkage problem.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 1. Page (1:399192) was not seen in the scan although its parent (1:25816) and previous (1:399207) refer to it. Check any previous errors.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1342627826, index ID 0: Page (1:399190) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 0, page (1:399190). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1342627826, index ID 0: Page (1:399191) could not be processed. See other errors for details.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 1. Page (1:399189) is missing a reference from previous page (1:399190). Possible chain linkage problem.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 1. Index node page (1:420444), slot 202 refers to child page (1:399190) and previous child (1:399191), but they were not encountered.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1342627826, index ID 1. Page (1:399191) was not seen in the scan although its parent (1:420444) and previous (1:399224) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'plumdbuser.PTJOBOPHISTORY' (object ID 1310627712).
CHECKDB found 0 allocation errors and 6 consistency errors in table 'plumdbuser.PTJOBLOGS' (object ID 1342627826).
CHECKDB found 0 allocation errors and 11 consistency errors in database 'Copy_of_Live_Plumdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Copy_of_Live_Plumdb ).

--------------------

This is the report by checkdb.

Table Orig Size Orig RowCnt After Size After RowCnt
PTJOBLOGS 405590 11372067 404697 11372066
PTJOBOPHISTORY 765 8637 671 8637

Above data shows table details before and after running repair, however rowcount in PTJOBOPHISTORY table still remains the same.





Pradeep Singh
Post #745358
Posted Wednesday, July 01, 2009 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 14,524, Visits: 10,413
The damage was in the cluster. It could be that you got lucky and it was an empty page that hadn't yet been dropped from the index.

p.s. you need to identify the cause of this. Several DBs corrupting at the same time suggests there's a problem with the IO system.



Gail Shaw

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

Post #745374
Posted Wednesday, July 01, 2009 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
Thanks Gail.
Yes, we had discussions with the SAN team and they confirmed there is no such issue at the moment.
I also told them to notify such issues to the database team (for servers hosting databases) asap so that we can restore the db from a clean backup(if need be). In this case, there was no option than to do away with the data.







Pradeep Singh
Post #745382
Posted Wednesday, July 01, 2009 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 14,524, Visits: 10,413
ps (7/1/2009)
I also told them to notify such issues to the database team (for servers hosting databases) asap so that we can restore the db from a clean backup(if need be).


You should be the one checking for issues. If they say there was no problem this time, do you really think that they're likely to notice a problem next time whatever it is happens?
http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-how-you-run-consistency-checks.aspx



Gail Shaw

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

Post #745436
Posted Wednesday, July 01, 2009 8:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
We use dbspi along with hp openview(OVSD/OVSC) and we do get timely notifications of any issues(which is good enough), however for this one, we didnot get any ticket as the system event log didnt mention anything about sql server. Ideally SAN/WITNEL teams should have recieved an alert for this. Since the customer we're supporting have several hundreds of servers, i dont think it's feasible to enter all servers and all databases and check for them.
Can you suggest something for such scenario (some scripts/not tools). Do you suggest we run dbcc checkdb every week so that such issues are recorded in the error log? We'll need to look for feasibility of this on each database basis as some of them are 24X7 and performance degradation because of running dbcc checkdb may not be acceptable in some cases.






Pradeep Singh
Post #745470
Posted Wednesday, July 01, 2009 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 AM
Points: 14,524, Visits: 10,413
ps (7/1/2009)
Do you suggest we run dbcc checkdb every week so that such issues are recorded in the error log?


Yes, absolutely, and someone needs to review the results after each run. If you don't you're highly unlikely to be able to catch corruption early enough to do anything useful about it.

See the blog post I referenced.



Gail Shaw

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

Post #745482
Posted Wednesday, July 01, 2009 9:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
Thanks Gail for providing deep insight into the issue.

I did go through the article you mentioned and another one
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
which did provide very useful information on how to reduce checkdb resource consumption.






Pradeep Singh
Post #745490
Posted Sunday, July 05, 2009 12:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
I was just going through some articles and came to know that i could have recovered only the affected pages if i had a clean backup instead of restoring the entire backup. The only condition i can think of is the affected pages must not have been changed after the full backup from which i'm trying to restore. Apart from this is there anything else i need to think of while doing page level restore? If it happened due to IO failure, should i assume that SQL Server was trying to write some data to these pages(In which case i would not be able to restore updated data?) Can this happen while reading too?
Also, how can i find out if all those pages belonged to the same extent or different extents?

Ref(Page restoration): http://www.mssqltips.com/tip.asp?tip=1645






Pradeep Singh
Post #747394
Posted Sunday, July 05, 2009 12:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698, Visits: 2,267
Ahh, I forgot that the corrupted database was in 2000 and page level restores is possible only in 2005 and later versions? My questions in the previous posts still remain if it were a 2005 database.





Pradeep Singh
Post #747395
« Prev Topic | Next Topic »

««123»»

Permissions Expand / Collapse