SQL Server 2005 – Internal Query Processor Error

Today morning while health check activity, we found that some jobs are failed in night with some unseen error related to query processor as shown below

Executed as user: NT AUTHORITY\SYSTEM. Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630). The step failed.

We tried executing job script manually it failed again with same error. Now decided to check database consistency by runing dbcc command for repective databases

DBCC checkdb(BiztalkDTADb)

After executing above we can see corruption at table level in the database for dta_ServiceInstances and dta_MessageInOutEvents table.

 

Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594043432960 (type LOB data). The off-row data node at page (1:11034), slot 21, text ID 19519438848 is referenced by page (1:61794), slot 4, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594060472320 (type In-row data): Errors found in off-row data with ID 19519438848 owned by data record identified by RID = (1:61794:4)
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_ServiceInstances’ (ID 469576711). Data row does not have a matching index row in the index ‘IX_ServiceInstances’ (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:61794:4) identified by (uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2′ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388’) with index values ‘dtEndTime = NULL and dtInsertionTimeStamp = ‘2012-07-14 10:49:18.530’ and uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2’ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388”.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_ServiceInstances’ (ID 469576711). Data row does not have a matching index row in the index ‘dta_ServiceInstances_index_dtStartTime’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:61794:4) identified by (uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2′ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388’) with index values ‘dtStartTime = ‘2012-07-14 10:44:50.930’ and uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2’ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388”.
There are 119439 rows in 37440 pages for object “dta_ServiceInstances”.
CHECKDB found 0 allocation errors and 4 consistency errors in table ‘dta_ServiceInstances’ (object ID 469576711).

 

DBCC results for ‘dta_MessageInOutEvents’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_ActivityId’ (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3907:3) identified by (uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921’ and nEventId = 14609188) with index values ‘uidServiceInstanceId = ‘E3B3082D-B420-4D54-80EB-05D60018BBDC’ and uidActivityId = ‘FC8DD571-D29C-4159-AB93-72576D5FFF86’ and uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921′ and nEventId = 14609188’.
Msg 8952, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Index row in index ‘dta_MessageInOutEvents_index_ActivityId’ (ID 2) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:14052:98) with values (uidServiceInstanceId = ‘186B813E-7A35-4804-A3C7-70C43F16A611’ and uidActivityId = ‘5D660D1B-D3A9-48F5-8845-100F25CADC93′ and uidMessageInstanceId = ’55D9096E-0083-4F97-8C5D-31CE4BE47C1F’ and nEventId = 14609195) pointing to the data row identified by (uidMessageInstanceId = ’55D9096E-0083-4F97-8C5D-31CE4BE47C1F’ and nEventId = 14609195).
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_dtTimestamp’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3907:3) identified by (uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921’ and nEventId = 14609188) with index values ‘dtTimestamp = ‘2012-07-14 10:44:50.353’ and dtInsertionTimeStamp = ‘2012-07-14 10:49:17.543′ and nEventId = 14609188’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_dtTimestamp’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:13016:20) identified by (uidMessageInstanceId = ‘F06F09DC-C2E5-409E-98FF-EA96FAE9BBFB’ and nEventId = 14609187) with index values ‘dtTimestamp = ‘2012-07-14 10:44:59.523’ and dtInsertionTimeStamp = ‘2012-07-14 10:49:17.430′ and nEventId = 14609187’.
There are 133950 rows in 5950 pages for object “dta_MessageInOutEvents”.
CHECKDB found 0 allocation errors and 4 consistency errors in table dta_MessageInOutEvents’ (object ID 917578307).

CHECKDB found 0 allocation errors and 8 consistency errors in database ‘BizTalkDTADb’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (BizTalkDTADb).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How to resolve Data corruption issue ?

we have to go for repair option recommended by dbcc result. We informed same to related team as this activity will take some time also we have to bring database offline for 15 to 20 mins. After getting confirmation from team we executed below commands.

1. To bring back database in single user mode for repairing database. Rollback immediate option will rollback all uncommitted data.
ALTER DATABASE BizTalkDTADb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Output: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

2. Now run the DBCC command with Repair_allow_data_loss option, this will remove all inconsistent with some data loss.
DBCC CheckDB (‘BizTalkDTADb’, REPAIR_ALLOW_DATA_LOSS)
Output:

DBCC results for ‘dta_ServiceInstances’.
Repair: The Clustered index successfully rebuilt for the object “dbo.dta_ServiceInstances” in database “BizTalkDTADb”.
Repair: Deleted record for object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594060472320 (type In-row data), on page (1:61794), slot 4. Indexes will be rebuilt.
Repair: Deleted off-row data column with ID 19519438848, for object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594060472320 (type In-row data) on page (1:61794), slot 4.
Repair: The Nonclustered index successfully rebuilt for the object “dbo.dta_ServiceInstances, IX_ServiceInstances” in database “BizTalkDTADb”.
Repair: The Nonclustered index successfully rebuilt for the object “dbo.dta_ServiceInstances, dta_ServiceInstances_index_dtStartTime” in database “BizTalkDTADb”.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594043432960 (type LOB data). The off-row data node at page (1:11034), slot 21, text ID 19519438848 is referenced by page (1:61794), slot 4, but was not seen in the scan.
The error has been repaired.
Msg 8929, Level 16, State 1, Line 1
Object ID 469576711, index ID 1, partition ID 72057594054836224, alloc unit ID 72057594060472320 (type In-row data): Errors found in off-row data with ID 19519438848 owned by data record identified by RID = (1:61794:4)
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 2 will be rebuilt.
The error has been repaired.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_ServiceInstances’ (ID 469576711). Data row does not have a matching index row in the index ‘IX_ServiceInstances’ (ID 2). Possible missing or invalid keys for the index row matching:
The error has been repaired.
Msg 8955, Level 16, State 1, Line 1
Data row (1:61794:4) identified by (uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2′ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388’) with index values ‘dtEndTime = NULL and dtInsertionTimeStamp = ‘2012-07-14 10:49:18.530’ and uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2’ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388”.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 469576711, index ID 3 will be rebuilt.
The error has been repaired.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_ServiceInstances’ (ID 469576711). Data row does not have a matching index row in the index ‘dta_ServiceInstances_index_dtStartTime’ (ID 3). Possible missing or invalid keys for the index row matching:
The error has been repaired.
Msg 8955, Level 16, State 1, Line 1
Data row (1:61794:4) identified by (uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2′ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388’) with index values ‘dtStartTime = ‘2012-07-14 10:44:50.930’ and uidServiceInstanceId = ‘37718D1E-E376-48A1-A489-3A2C005CD4F2’ and uidActivityId = ’69F649B6-0223-43D3-8AB0-DEC8FEC47388”.

There are 119688 rows in 37440 pages for object “dta_ServiceInstances”.
CHECKDB found 0 allocation errors and 4 consistency errors in table ‘dta_ServiceInstances’ (object ID 469576711).
CHECKDB fixed 0 allocation errors and 4 consistency errors in table ‘dta_ServiceInstances’ (object ID 469576711).

3.Rebuild all indexes from corrupted table.

ALTER INDEX ALL ON [dta_ServiceInstances] REBUILD

 

DBCC results for ‘dta_MessageInOutEvents’.
Repair: Successfully inserted row in index “dbo.dta_MessageInOutEvents, dta_MessageInOutEvents_index_ActivityId” in database “BizTalkDTADb”.
Repair: Successfully deleted row in index “dbo.dta_MessageInOutEvents, dta_MessageInOutEvents_index_ActivityId” in database “BizTalkDTADb”.
Repair: Successfully inserted row in index “dbo.dta_MessageInOutEvents, dta_MessageInOutEvents_index_dtTimestamp” in database “BizTalkDTADb”.
Repair: Successfully inserted row in index “dbo.dta_MessageInOutEvents, dta_MessageInOutEvents_index_dtTimestamp” in database “BizTalkDTADb”.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_ActivityId’ (ID 2). Possible missing or invalid keys for the index row matching:
The error has been repaired.
Msg 8955, Level 16, State 1, Line 1
Data row (1:3907:3) identified by (uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921’ and nEventId = 14609188) with index values ‘uidServiceInstanceId = ‘E3B3082D-B420-4D54-80EB-05D60018BBDC’ and uidActivityId = ‘FC8DD571-D29C-4159-AB93-72576D5FFF86’ and uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921′ and nEventId = 14609188’.
Msg 8952, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Index row in index ‘dta_MessageInOutEvents_index_ActivityId’ (ID 2) does not match any data row. Possible extra or invalid keys for:
The error has been repaired.
Msg 8956, Level 16, State 1, Line 1
Index row (1:14052:98) with values (uidServiceInstanceId = ‘186B813E-7A35-4804-A3C7-70C43F16A611’ and uidActivityId = ‘5D660D1B-D3A9-48F5-8845-100F25CADC93′ and uidMessageInstanceId = ’55D9096E-0083-4F97-8C5D-31CE4BE47C1F’ and nEventId = 14609195) pointing to the data row identified by (uidMessageInstanceId = ’55D9096E-0083-4F97-8C5D-31CE4BE47C1F’ and nEventId = 14609195).
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_dtTimestamp’ (ID 3). Possible missing or invalid keys for the index row matching:
The error has been repaired.
Msg 8955, Level 16, State 1, Line 1
Data row (1:3907:3) identified by (uidMessageInstanceId = ‘FA04840D-9429-4F00-8EF4-3D0A7A4DA921’ and nEventId = 14609188) with index values ‘dtTimestamp = ‘2012-07-14 10:44:50.353’ and dtInsertionTimeStamp = ‘2012-07-14 10:49:17.543′ and nEventId = 14609188’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘dta_MessageInOutEvents’ (ID 917578307). Data row does not have a matching index row in the index ‘dta_MessageInOutEvents_index_dtTimestamp’ (ID 3). Possible missing or invalid keys for the index row matching:
The error has been repaired.
Msg 8955, Level 16, State 1, Line 1
Data row (1:13016:20) identified by (uidMessageInstanceId = ‘F06F09DC-C2E5-409E-98FF-EA96FAE9BBFB’ and nEventId = 14609187) with index values ‘dtTimestamp = ‘2012-07-14 10:44:59.523’ and dtInsertionTimeStamp = ‘2012-07-14 10:49:17.430′ and nEventId = 14609187’.
There are 134440 rows in 5979 pages for object “dta_MessageInOutEvents”.
CHECKDB found 0 allocation errors and 4 consistency errors in table ‘dta_MessageInOutEvents’ (object ID 917578307).
CHECKDB fixed 0 allocation errors and 4 consistency errors in table ‘dta_MessageInOutEvents’ (object ID 917578307).

— Rebuild the index
ALTER INDEX ALL ON [dta_MessageInOutEvents] REBUILD

4.Bring back database online for users by executing below command

ALTER DATABASE BizTalkDTADb SET MULTI_USER

Now all inconsistent data has been removed from database and executed the same job again which was failed in the night this time it completed successfully.

 

Leave a Reply

  

  

  


− 2 = 7