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»»

Logical consistency error Expand / Collapse
Author
Message
Posted Tuesday, January 1, 2013 9:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 AM
Points: 54, Visits: 255
Hi,

Often am getting logical consistency error in one my production server. Recently i did DBCC with allow dataloss option, to correct the inconsistency. Now i see the below error in the database.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:6743211; actual 0:0). It occurred during a read of page (1:6743211) in database ID 11 at offset 0x00000cdc956000

Again do I need to repair the database? Why this inconsistency is happening often?

Thanks,
Dev.
Post #1401646
Posted Wednesday, January 2, 2013 2:37 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 @ 8:30 AM
Points: 42,756, Visits: 35,847
Please run the following and post the full and complete unedited output.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

If you're getting repeated corruptions, there's something wrong in the IO subsystem. Check logs, check drivers, firmware, etc.



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 #1401711
Posted Wednesday, January 2, 2013 10:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 AM
Points: 54, Visits: 255
Am unable to do DBCC checkdb also.
It is erroring with temp db out of space.

Month back , i tried to run dbcc when my application was down. That time i got the output.
Shall i run with tablock option? Is there any other option where i can perform DBCC without taking application down. Any Option other than taking the db in single user mode.

Thanks,
Dev.
Post #1402184
Posted Thursday, January 3, 2013 12:56 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 @ 8:30 AM
Points: 42,756, Visits: 35,847
Post the output you got (everything). I can't tell anything from a description of the messages. (no, not with tablock)

Repair is not the recommended way to fix corruption, it's for cases where there's no clean backup. The recommended fix for corruption is to restore a clean backup



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 #1402212
Posted Thursday, January 3, 2013 3:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 AM
Points: 54, Visits: 255
This is the output for the DBCC checkdb command

I executed DBCC checkdb (db_name) with all_errormsgs;

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for <database_name >
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database <database name>
Post #1402272
Posted Thursday, January 3, 2013 6:00 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 @ 8:30 AM
Points: 42,756, Visits: 35,847
Restore from a good backup

And do some investigation of that IO subsystem, maybe move the DB elsewhere (different disks)



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 #1402318
Posted Thursday, January 3, 2013 8:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 AM
Points: 54, Visits: 255
The problem is the good backup of this database got expired.

Kindly clarify my below questions

When you say move the DB to different disk? I have nearly 9 DB's data files on the same drive.Do I need to move all the 9 DB's data file to other drive??

Even other db's mdf files stay in this drive healthy?? Why this is getting corrupted?

Post #1402640
Posted Friday, January 4, 2013 12:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 2:04 AM
Points: 7,004, Visits: 8,444
Whenever I suspect the IO system to be unreliable, I move all DB stuff that is on it !
No doubt.
I wouldn't take the risk of down time or losing data because of HW failures of an IO system that is unreliable.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1402728
Posted Friday, January 4, 2013 2:18 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 @ 8:30 AM
Points: 42,756, Visits: 35,847
devanandiiway (1/3/2013)
The problem is the good backup of this database got expired.


Then you have a serious problem, because there's no way you can repair this error. You need to restore a clean backup here.

When you say move the DB to different disk? I have nearly 9 DB's data files on the same drive.Do I need to move all the 9 DB's data file to other drive??


Well that or risk the others becoming corrupted

Even other db's mdf files stay in this drive healthy?? Why this is getting corrupted?


As I said, IO subsystem problems of some form. You need to check the IO subsystem (everything from the drivers to the disks) and look for problems.



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 #1402754
Posted Friday, January 4, 2013 3:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 AM
Points: 54, Visits: 255
Do you mean that I need to move all the mdf from that drive?

Is there anyway I can repair the hardware? Since it is occuring often.
Post #1402784
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse