SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Consistency errors


DB Consistency errors

Author
Message
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 1865
Vishal

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

"Keep Trying"
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89471 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


Vishal Singh
Vishal Singh
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 517

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
Vishal Singh
Vishal Singh
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 517
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
Vishal Singh
Vishal Singh
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 517
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
Vishal Singh
Vishal Singh
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 517
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89471 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89471 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89471 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89471 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search