Consistency Errors... - 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

Consistency Errors...

Author

Topic

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2005-08-01 : 12:31:21

Sorry as the last Topic thread was closed...

I just had the same problem. I put the DB in single_user mode then ran a DBCC CheckTable with Repair and then had 80 consis errors. A DBCC CheckTable with Repiar_Rebuild didn't fix it either. A DBCC Reindex of the Table cleared all errors without data loss and another CHeckTable was clean as well. Any ideas?

Below are the results of a DBCC CHeckTable.

Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:2) identified by (RID = (4:8:2) WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 3 and Node = 1) has index values (WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 3 and ImageID = '0053100230106' and WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 3 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:3) identified by (RID = (4:8:3) WorkOrder = 5339863 and Instruction = '0053010240041 ' and order_seq = 1 and Node = 1) has index values (WorkOrder = 5339863 and Instruction = '0053010240041 ' and order_seq = 1 and ImageID = '0053010240041' and WorkOrder = 5339863 and Instruction = '0053010240041 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:4) identified by (RID = (4:8:4) WorkOrder = 5339992 and Instruction = '0053021340004 ' and order_seq = 1 and Node = 1) has index values (WorkOrder = 5339992 and Instruction = '0053021340004 ' and order_seq = 1 and ImageID = '0053021340004' and WorkOrder = 5339992 and Instruction = '0053021340004 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:5) identified by (RID = (4:8:5) WorkOrder = 5339992 and Instruction = '0053021340005 ' and order_seq = 2 and Node = 1) has index values (WorkOrder = 5339992 and Instruction = '0053021340005 ' and order_seq = 2 and ImageID = '0053021340005' and WorkOrder = 5339992 and Instruction = '0053021340005 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:6) identified by (RID = (4:8:6) WorkOrder = 5339993 and Instruction = '0053021340011 ' and order_seq = 1 and Node = 1) has index values (WorkOrder = 5339993 and Instruction = '0053021340011 ' and order_seq = 1 and ImageID = '0053021340011' and WorkOrder = 5339993 and Instruction = '0053021340011 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:7) identified by (RID = (4:8:7) WorkOrder = 5339994 and Instruction = '0053022740041 ' and order_seq = 1 and Node = 1) has index values (WorkOrder = 5339994 and Instruction = '0053022740041 ' and order_seq = 1 and ImageID = '0053022740041' and WorkOrder = 5339994 and Instruction = '0053022740041 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:8) identified by (RID = (4:8:8) WorkOrder = 5339994 and Instruction = '0053022740052 ' and order_seq = 2 and Node = 1) has index values (WorkOrder = 5339994 and Instruction = '0053022740052 ' and order_seq = 2 and ImageID = '0053022740052' and WorkOrder = 5339994 and Instruction = '0053022740052 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:9) identified by (RID = (4:8:9) WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 1 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 1 and ImageID = '0053021340034' and WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:10) identified by (RID = (4:8:10) WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 101 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 101 and ImageID = '0053021340034' and WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 101 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:11) identified by (RID = (4:8:11) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 2 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 2 and ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:12) identified by (RID = (4:8:12) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 3 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 3 and ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 3 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:13) identified by (RID = (4:8:13) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 102 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 102 and ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 102 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:14) identified by (RID = (4:8:14) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 103 and Node = 1) has index values (WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 103 and ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 103 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:0) identified by (RID = (4:8:0) WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053100230106' and WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:1) identified by (RID = (4:8:1) WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 2 and Node = 1) has index values (ImageID = '0053100230106' and WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:2) identified by (RID = (4:8:2) WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 3 and Node = 1) has index values (ImageID = '0053100230106' and WorkOrder = 5339343 and Instruction = '0053100230106 ' and order_seq = 3 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:3) identified by (RID = (4:8:3) WorkOrder = 5339863 and Instruction = '0053010240041 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053010240041' and WorkOrder = 5339863 and Instruction = '0053010240041 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:4) identified by (RID = (4:8:4) WorkOrder = 5339992 and Instruction = '0053021340004 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053021340004' and WorkOrder = 5339992 and Instruction = '0053021340004 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:5) identified by (RID = (4:8:5) WorkOrder = 5339992 and Instruction = '0053021340005 ' and order_seq = 2 and Node = 1) has index values (ImageID = '0053021340005' and WorkOrder = 5339992 and Instruction = '0053021340005 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:6) identified by (RID = (4:8:6) WorkOrder = 5339993 and Instruction = '0053021340011 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053021340011' and WorkOrder = 5339993 and Instruction = '0053021340011 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:7) identified by (RID = (4:8:7) WorkOrder = 5339994 and Instruction = '0053022740041 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053022740041' and WorkOrder = 5339994 and Instruction = '0053022740041 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:8) identified by (RID = (4:8:8) WorkOrder = 5339994 and Instruction = '0053022740052 ' and order_seq = 2 and Node = 1) has index values (ImageID = '0053022740052' and WorkOrder = 5339994 and Instruction = '0053022740052 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:9) identified by (RID = (4:8:9) WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 1 and Node = 1) has index values (ImageID = '0053021340034' and WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 1 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:10) identified by (RID = (4:8:10) WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 101 and Node = 1) has index values (ImageID = '0053021340034' and WorkOrder = 5339996 and Instruction = '0053021340034 ' and order_seq = 101 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:11) identified by (RID = (4:8:11) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 2 and Node = 1) has index values (ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 2 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:12) identified by (RID = (4:8:12) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 3 and Node = 1) has index values (ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 3 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:13) identified by (RID = (4:8:13) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 102 and Node = 1) has index values (ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 102 and Node = 1).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Order_Item_Images' (ID 829962033). Missing or invalid key in index 'IX_Order_Item_Images_1' (ID 3) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (4:8:14) identified by (RID = (4:8:14) WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 103 and Node = 1) has index values (ImageID = '0053021340044' and WorkOrder = 5339996 and Instruction = '0053021340044 ' and order_seq = 103 and Node = 1).
DBCC results for 'Order_Item_Images'.
There are 9816281 rows in 221056 pages for object 'Order_Item_Images'.
CHECKTABLE found 0 allocation errors and 28 consistency errors in table 'Order_Item_Images' (object ID 829962033).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (PIP.dbo.Order_Item_Images ).

Thanks DeWayne McCallie DBA
Olan Mills, Inc.
Chattanooga TN...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 12:57:59

Hi DeWayne,

Let me establish a sequence of events to make sure I've got it right:

a) you ran CHECKTABLE and saw errors
b) you ran CHECKTABLE with REPAIR_FAST to repair these
c) you ran CHECKTABLE WITH REPAIR_REBUILD?
d) you ran DBREINDEX of the table

Assuming this is the sequence of events, do you have the output from steps b and c? (I'm assuming the output from step a is what you posted above). I'd like to see if the errors in steps b and c match those in step a. I'm guessing they don't, indicating a problem with the clustered index on which non-clustered index IDs 2 and 3 are based or a problem with non-clustered index maintenance, which is why doing a DBREINDEX fixed the problem.

To repair these errors, CHECKTABLE does single inserts/deletes in the non-clustered indexes rather than an entire index rebuild, even when REPAIR_REBUILD is specified. (Note that the repair level specified doesn't change what repair will do to fix a particular problem, it just allows it to perform more invasive repairs if necessary).

Some other questions:

1) what version are you running?
2) have you checked the SQL errorlog and Windows event logs for evidence of h/w corruption?

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2005-08-01 : 13:28:38

Thanks for the quick reply.

I came in this morning to applications not working. The SQL log had these errors:

Could not find the index entry for RID '16562a4a039303735303732333530363435201010400' in index page (1:318765), index ID 2, database 'PIP'..

and

Error: 644, Severity: 21, State: 5

The applications people indicated that they thought that something was wrong with the Order_Item_Images table right as I was getting ready to run a CheckDB. Therefore, I ran a CheckTable on Order_Item_Images table and the output from that run was the one that I saved/posted.

Then your order of events is correct. CheckTable, then CheckTable Repair_Fast (it came back with errors fixed.) Then I re-ran CheckTable again and had 80 consis errors. Then I ran CheckTable with Repair_Rebuild (and it completed too fast.) I didn't save the output of any of the DBCC's except the 1st run. Finally since there were no allocation errors and the minimum fix option (from the DBCC) was repair_fast, I just ran a DBCC Reindex on the table followed by another CHeckTable and all was clean. I have no errors in the NT Event log.

This server runs SQL/2k Enterprise with sp3a on a Win2K Server.

Thanks in Advance Paul,

DeWayne

p.s. Yes I do a nightly backup as well as hourly tran log backups to DISK. I didn't want to restore the whole DB unless that was the only option...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 13:38:32

Can you confirm whether the table has a clustered index or is a heap? From the error it looks like a heap, in which case it may be a known bug that's causing this.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2005-08-01 : 13:50:06

CREATE TABLE [dbo].[Order_Item_Images] (
[WorkOrder] [int] NOT NULL ,
[Instruction] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[order_seq] [smallint] NOT NULL ,
[Node] [smallint] NOT NULL ,
[ImageID] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Image_path] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CropX] [float] NULL ,
[CropY] [float] NULL ,
[CropLength] [float] NULL ,
[CropWidth] [float] NULL ,
[PO_Density] [smallint] NULL ,
[PO_Red] [smallint] NULL ,
[PO_Green] [smallint] NULL ,
[PO_Blue] [smallint] NULL ,
[po_contrast] [smallint] NULL ,
[po_gamma] [decimal](4, 2) NULL ,
[FrameID] [smallint] NULL ,
[MaskID] [smallint] NULL ,
[field_lock] [smallint] NULL
) ON [JobData]
GO

ALTER TABLE [dbo].[Order_Item_Images] WITH NOCHECK ADD
CONSTRAINT [PK_Order_Item_Images] PRIMARY KEY CLUSTERED
(
[WorkOrder],
[Instruction],
[order_seq],
[Node]
) WITH FILLFACTOR = 80 ON [JobData]
GO

ALTER TABLE [dbo].[Order_Item_Images] ADD
CONSTRAINT [DF_Order_Item_Images_CropX] DEFAULT (50.0) FOR [CropX],
CONSTRAINT [DF_Order_Item_Images_CropY] DEFAULT (50.0) FOR [CropY],
CONSTRAINT [DF_Order_Item_Images_CropLength] DEFAULT (100.0) FOR [CropLength],
CONSTRAINT [DF_Order_Item_Images_CropWidth] DEFAULT (100.0) FOR [CropWidth],
CONSTRAINT [DF_Order_Item_Images_PO_Density] DEFAULT (0) FOR [PO_Density],
CONSTRAINT [DF_Order_Item_Images_PO_Red] DEFAULT (0) FOR [PO_Red],
CONSTRAINT [DF_Order_Item_Images_PO_Green] DEFAULT (0) FOR [PO_Green],
CONSTRAINT [DF_Order_Item_Images_PO_Blue] DEFAULT (0) FOR [PO_Blue],
CONSTRAINT [DF_Order_Item_Images_po_contrast] DEFAULT (0) FOR [po_contrast],
CONSTRAINT [DF_Order_Item_Images_po_gamma] DEFAULT (1.0) FOR [po_gamma]
GO

CREATE INDEX [IX_Order_Item_Images] ON [dbo].[Order_Item_Images]([WorkOrder], [Instruction], [order_seq], [ImageID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [IX_Order_Item_Images_1] ON [dbo].[Order_Item_Images]([ImageID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [IX_Order_Item_Images_2] ON [dbo].[Order_Item_Images]([WorkOrder]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Order_Item_Images] ADD
CONSTRAINT [FK_Order_Item_Images_Frame_Table] FOREIGN KEY
(
[FrameID]
) REFERENCES [dbo].[Frame_Table] (
[FrameID]
),
CONSTRAINT [FK_Order_Item_Images_Image_Data] FOREIGN KEY
(
[ImageID]
) REFERENCES [dbo].[Image_Data] (
[ImageID]
),
CONSTRAINT [FK_Order_Item_Images_Mask_Table] FOREIGN KEY
(
[MaskID]
) REFERENCES [dbo].[Mask_Table] (
[MaskID]
),
CONSTRAINT [FK_Order_Item_Images_Mask_Table1] FOREIGN KEY
(
[MaskID]
) REFERENCES [dbo].[Mask_Table] (
[MaskID]
),
CONSTRAINT [FK_Order_Item_Images_Order_Items1] FOREIGN KEY
(
[WorkOrder],
[Instruction],
[order_seq]
) REFERENCES [dbo].[Order_Items] (
[WorkOrder],
[Instruction],
[order_seq]
) ON UPDATE CASCADE
GO

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2005-08-01 : 13:56:20

sorry Clustered...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 14:15:13

Hmm - ok its not that known bug.

Do you have write-caching turned on?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2005-08-01 : 14:20:09

Yes. The disk subsystem is an IBM EXP500 SAN (Fiber attach) with battery backup cache. Data is on Raid 5, Index on Raid 5, Logs on Raid 1...64k stripe on the RAID 5's.

No hardware errors that I am aware of...

Thanks

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 16:29:30

quote:
Originally posted by DMcCallie

Yes. The disk subsystem is an IBM EXP500 SAN (Fiber attach) with battery backup cache. Data is on Raid 5, Index on Raid 5, Logs on Raid 1...64k stripe on the RAID 5's.

No hardware errors that I am aware of...

Thanks

ok - you need to turn write-caching off as it can cause all sorts of problems. See the whitepaper at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for more details (look for the section headed Forced Unit Access). You may also need to install Win2k SP3 or download the FUA utility to ensure the FUA bit is really switched on (again, see the whitepaper for details).

Basically, write-caching allows the h/w to signal an IO has completed before it hits the oxide, so there's no guarantee that a write makes it to disk properly.

Try the following to see if flushing the disk h/w caches solves the problem:

DBCC DROPCLEANBUFFERS
DBCC CHECKDB (PIP) WITH NO_INFOMSGS

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources