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 Monday, July 27, 2009 10:59 PM


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


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760489
Posted Monday, July 27, 2009 11:50 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 2, 2014 12:02 AM
Points: 185, Visits: 726
can you post some sample error message

Rajesh Kasturi
Post #760508
Posted Monday, July 27, 2009 11:55 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134
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.


Thanks
Chandra Mohan
Post #760511
Posted Tuesday, July 28, 2009 12:23 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
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.


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #760528
Posted Tuesday, July 28, 2009 12:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 5, 2013 7:41 AM
Points: 1,146, Visits: 230
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



Post #760531
Posted Tuesday, July 28, 2009 12:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 5, 2013 7:41 AM
Points: 1,146, Visits: 230
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



Post #760540
Posted Tuesday, July 28, 2009 12:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 5, 2013 7:41 AM
Points: 1,146, Visits: 230
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



Post #760543
Posted Tuesday, July 28, 2009 12:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134
Hi,

Please post complete erorr messages. As this will help to find out any other issues also.


Thanks
Chandra Mohan
Post #760545
Posted Tuesday, July 28, 2009 12:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134
Hi,

Please post complete erorr messages. As this will help to find out any other issues also.


Thanks
Chandra Mohan
Post #760546
Posted Tuesday, July 28, 2009 12:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134
Hi,

Please post complete erorr messages. As this will help to find out any other issues also.


Thanks
Chandra Mohan
Post #760547
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse