Recurring Inconsistencies - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Recurring Inconsistencies

Author

Topic

MFlanagan
Starting Member

10 Posts

Posted - 2007-02-28 : 12:32:18

I have a situation where a common table within multiple databases across multiple servers is randomly getting corrupt. The database schema is identical for each database. There are approx. 200 copies of the database on each server. I am seeing one or two errors per week. There doesn't seem to be any similarities between the databases that are effected... It is always the same table that gets corrupted. The mulitple servers are using a SAN but since it is different databases / common table I have a hard time believing it is hardware related.

The error that the application throws is:
Microsoft OLE DB Provider for ODBC Drivers (-2147467259) [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find the index entry for RID '16b700000000000000030002' in index page (1:5366), index ID 0, database 'DISOPT'.

When running a DBCC on the table the following is returned:

Database DISOPT consistency errors in table TF_FILE_METADATA_VALUES fixed no data loss reported.
DBCC results for 'TF_FILE_METADATA_VALUES'.
The error has been repaired.
Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'MUTGEN'.
There are 277 rows in 2 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).

This corrects the problem and the app functions as normal. The errors are random across databases but ALWAYS the same table.

Any thoughts?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-02-28 : 13:07:06

Which version of SQL Server are you using? I don't recognize the first line of the CHECKDB output from SQL 2000 or 2005.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

MFlanagan
Starting Member

10 Posts

Posted - 2007-02-28 : 13:13:32

Sorry. SQL 2000 SP4

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-02-28 : 13:18:13

Ah - I see - I guess the first line was your comment. Can you post the completed output from CHECKDB please? Is the output the same for all databases? What's the schema of the table and its indexes?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

MFlanagan
Starting Member

10 Posts

Posted - 2007-02-28 : 13:26:42

I'll have to wait for another inconsistancy to happen to get a new CHECKDB as I didn't save it - shouldn't be long :-)

Schema:
[TFFMV_ID] [int] IDENTITY(1,1) NOT NULL,
[TFFMFSV_ID] [int] NOT NULL,
[DOCFILE_ID] [int] NULL,
[LENGTH] [int] NOT NULL CONSTRAINT [DF_TF_FILE_METADATA_VALUES_LENGTH] DEFAULT (0),
[VALUE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUBMISSION_FILE_ID] [int] NOT NULL CONSTRAINT [DF_TF_FILE_METADATA_VALUES_SUBMISSION_FILE_ID] DEFAULT (0),
CONSTRAINT [PK_TF_FILE_METADATA_VALUES] PRIMARY KEY NONCLUSTERED

MFlanagan
Starting Member

10 Posts

Posted - 2007-03-05 : 10:27:52

Here are the results for DBCC CheckDB(Optics):

I stripped out all the tables that are not reporting problems to keep it short..

DBCC results for 'OPTICS'.
DBCC results for 'sysobjects'.
There are 1875 rows in 40 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 1005 rows in 43 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 5638 rows in 141 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 1605 rows in 387 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 117 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 14 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 7124 rows in 48 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 139 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
.
.
.
DBCC results for 'KEYWDATA'.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:4272) (level 0) is not >= the key value in the parent (1:7688) slot 1.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:6805) (level 0) is not >= the key value in the parent (1:7688) slot 3.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:6806) (level 0) is not >= the key value in the parent (1:7688) slot 2.
There are 0 rows in 1 pages for object 'KEYWDATA'.
.
.
.
DBCC results for 'TF_FILE_METADATA_VALUES'.
There are 531 rows in 4 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
.
.
.
CHECKDB found 0 allocation errors and 3 consistency errors in database 'optics'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (optics ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Here are the results of DBCC CHECKDB ('OPTICS', repair_rebuild)

.
.
.
DBCC results for 'AUTHOR_INFO'.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:4272) (level 0) is not >= the key value in the parent (1:7688) slot 1.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:6805) (level 0) is not >= the key value in the parent (1:7688) slot 3.
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1332199796, index ID 1. The low key value on page (1:6806) (level 0) is not >= the key value in the parent (1:7688) slot 2.
There are 584 rows in 9 pages for object 'AUTHOR_INFO'.
DBCC results for 'CUSTOM_IMAGE_TYPES'.
There are 3 rows in 1 pages for object 'CUSTOM_IMAGE_TYPES'.
DBCC results for 'REVHIST'.
There are 430 rows in 7 pages for object 'REVHIST'.
DBCC results for 'LOGIN_CONFIGURATION'.
There are 1 rows in 1 pages for object 'LOGIN_CONFIGURATION'.
DBCC results for 'TF_FILE_METADATA_VALUES'.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'optics'.
There are 531 rows in 4 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
CHECKDB fixed 0 allocation errors and 3 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
.
.
.
CHECKDB found 0 allocation errors and 3 consistency errors in database 'optics'.
CHECKDB fixed 0 allocation errors and 3 consistency errors in database 'optics'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-05 : 11:01:51

This looks like a known bug in SQL Server. Please call Product Support, tell them I told you to call and reference SQL Server 2000 bug# 475081. If you get any hassle, send me email.

Cheers

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

MFlanagan
Starting Member

10 Posts

Posted - 2007-03-05 : 13:23:01

Thanks Paul. I will do as you suggest and report back on my results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources