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

DB Consistency errors Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 1:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Vishal

First thing - Do you have proper backups?
check this article http://www.sqlservercentral.com/articles/65804/


"Keep Trying"
Post #760552
Posted Tuesday, July 28, 2009 1:07 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 @ 7:54 AM
Points: 42,768, Visits: 35,867
The root cause will be IO subsystem problems. Corruption is (99% of the time) the result of problems somewhere in the IO path. Check anti-virus, check drivers, check HBAs, check SAN switches, check the SAN fabric, etc....

Why on earth did you decide to repair? If the errors were recent and your backups good, you should have been able to restore fully with no data loss, and probably a fair bit faster.



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 #760555
Posted Tuesday, July 28, 2009 2:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487

You can get consistency error mostly for
the values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005 and later, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error. (Source - Microsoft).




My databases are on 2000 only where I believe there is no DATA_PURITY option available?

Also, since couple of months there is no changes in any of my procs and this consistency errors start coming in just since few days.

wouldn't it put the possibility of inserting invalid data to question?

Apart of any disk level problem (which I am checking now) what else could be the possibility?


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760595
Posted Tuesday, July 28, 2009 2:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
GilaMonster (7/28/2009)
The root cause will be IO subsystem problems. Corruption is (99% of the time) the result of problems somewhere in the IO path. Check anti-virus, check drivers, check HBAs, check SAN switches, check the SAN fabric, etc....

Why on earth did you decide to repair? If the errors were recent and your backups good, you should have been able to restore fully with no data loss, and probably a fair bit faster.


For restoring from backups I had to go 15 days back because I have a full backup running everyday (WITN INIT) and the conistency error I found was started 10-14 days back.
pretty much through...isn't?
restoring may caused loosing others data (pertty much more than what I lost while repairing with data_loss).


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760596
Posted Tuesday, July 28, 2009 3:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
Additionally, Look on to the error message Its Error 8964:

Was there any other alternative in my situation?
as there any other alternative in my situation.?


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760599
Posted Tuesday, July 28, 2009 3:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
Chirag (7/28/2009)
Vishal

First thing - Do you have proper backups?
check this article http://www.sqlservercentral.com/articles/65804/


Hi Chirag, Thanx Mann I have been to this article and I found its very decent and nice.


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760601
Posted Tuesday, July 28, 2009 3:12 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 @ 7:54 AM
Points: 42,768, Visits: 35,867
Vishal Singh (7/28/2009)

For restoring from backups I had to go 15 days back because I have a full backup running everyday (WITN INIT) and the conistency error I found was started 10-14 days back.
pretty much through...isn't?
restoring may caused loosing others data (pertty much more than what I lost while repairing with data_loss).


Then you need to seriously sit down and review both your backup strategy (because it failed you here) and the frequency that you run consistency checks.



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 #760604
Posted Tuesday, July 28, 2009 3:14 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 @ 7:54 AM
Points: 42,768, Visits: 35,867
Vishal Singh (7/28/2009)
Additionally, Look on to the error message Its Error 8964:

Yup, I saw. What of it? It's a fairly standard corruption message saying that there's damage to the blob pages

Was there any other alternative in my situation?
as there any other alternative in my situation.?


Since the minimum level to repair was specified as repair_allow_data_loss, your options are limited to repair with data loss or restore from 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 #760605
Posted Tuesday, July 28, 2009 3:20 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 @ 7:54 AM
Points: 42,768, Visits: 35,867
manoj2001 (7/28/2009)
So please check in your procedure wherver you are importing data into database.


It's not possible to insert out-of-range values via stored procedures. The SQL query processor will catch that. Data purity errors (which, by the way, these are not) are generally the result of IO corruption or buggy ODBC/OLEDB drivers that do bulk data loads



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 #760611
Posted Tuesday, July 28, 2009 3:23 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 @ 7:54 AM
Points: 42,768, Visits: 35,867
Vishal Singh (7/28/2009)
My databases are on 2000 only where I believe there is no DATA_PURITY option available?


Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.



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 #760615
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse