July 27, 2009 at 10:59 pm
Hi,
Couple of my production databases reported Consistency error
(more than 800 errors per databases).
CHECKALLOC reported 0 errors.
I repaired them with allow_data_loss as this was the minimum DBCC repair level.
My DBs are fine now but I want to check when exactly those consistency errors started coming in and then to find out the root cause.
I am exploring everything so far and so how looking out for similar expertise from you guys. Thanks
VS
July 27, 2009 at 11:50 pm
can you post some sample error message
Rajesh Kasturi
July 27, 2009 at 11:55 pm
Hi Vishal,
I am surprised that without root cause analysis you used dbcc with allow data loss and that too in production. Can you post the error messages which you got before applying dbcc with allow data loss.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 28, 2009 at 12:23 am
Chandu (7/27/2009)
Hi Vishal,I am surprised that without root cause analysis you used dbcc with allow data loss and that too in production. Can you post the error messages which you got before applying dbcc with allow data loss.
Because conistency was since two weeks and we had no times to do an RCA first and then repair the DB so I am doing RCA now.
the Smaple errors were like this:
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 206623779. The text, ntext, or image node at page (1:4846), slot 7, text ID 18859360256 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 206623779. The text, ntext, or image node at page (1:4846), slot 9, text ID 18859425792 is not referenced.
July 28, 2009 at 12:25 am
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).
So please check in your procedure wherver you are importing data into database.
Manoj
July 28, 2009 at 12:36 am
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).
So please check in your procedure wherver you are importing data into database.
Manoj
July 28, 2009 at 12:47 am
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).
So please check in your procedure wherver you are importing data into database.
Manoj
July 28, 2009 at 12:50 am
Hi,
Please post complete erorr messages. As this will help to find out any other issues also.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 28, 2009 at 12:52 am
Hi,
Please post complete erorr messages. As this will help to find out any other issues also.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 28, 2009 at 12:54 am
Hi,
Please post complete erorr messages. As this will help to find out any other issues also.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 28, 2009 at 1:00 am
Vishal
First thing - Do you have proper backups?
check this article http://www.sqlservercentral.com/articles/65804/
"Keep Trying"
July 28, 2009 at 1:07 am
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
July 28, 2009 at 2:41 am
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?
July 28, 2009 at 2:47 am
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).
July 28, 2009 at 3:06 am
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply