question

Sajas avatar image
Sajas asked

BizTalk Message Box Indexing Issue

Hi I have a BizTalk 2004 application in production which uses SQL server 2000 as the backend. Iam getting the following error in the event viewer when the minute scheduled job 'PurgeSubscriptionsJob_BizTalkMsgBoxDb' runs to clean up the Message box database.

                    
Error: 644, Severity: 21, State: 3                    
Could not find the index entry for RID '36dcebf0b8037de847aa27b79ea3caca6b03000002003a003e00e701180008d0003443006100630068006500520065006600'                     
in index page (1:984), index ID 0, database 'BizTalkMsgBoxDb'.                    

I did a CHECKDB and found the following errors I got

                    
Server: Msg 8951, Level 16, State 1, Line 1                    
Table error: Table 'Subscription' (ID 421576540). Missing or invalid key in index 'IX_Subscription_2' (ID 3) for the row:                    
Server: Msg 8955, Level 16, State 1, Line 1                    
Data row (1:35931:0) identified by (RID = (1:35931:0) uidPredicateGroupID = A06412EB-6ACD-4696-BE35-FAAFE12FD904 and ? = NULL) has index values (uidServiceID = BB3A1470-F5C4-47C3-B71F-EAABC260FBD0 and uidInstanceID = 095832EC-3708-4DE3-BEFF-69B7FCB000AF and uidSubID = NULL and uidPredicateGroupID = A06412EB-6ACD-4696-BE35-FAAFE12FD904 and ? = NULL).                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:21912:12) with values (uidPredicateGroupID = 4ADF185B-8E72-431A-81DD-9EF2F9052E16 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 628) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:81095:0) with values (uidPredicateGroupID = CAE43463-DB80-4F42-9B48-C5F46A2EEE11 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 633) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:297592:19) with values (uidPredicateGroupID = D73519FB-9B51-45FD-BDCE-BF8003B43C3B and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 625) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:326088:30) with values (uidPredicateGroupID = 0394E7EA-C669-40CF-A5C4-C60501944CB4 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 622) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:356445:0) with values (uidPredicateGroupID = 2BC7B960-4BB7-4BC9-B77E-999C807C18C1 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 634) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:614135:23) with values (uidPredicateGroupID = F0652183-146C-4BA1-9545-1F12BB09AAF7 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 623) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:757039:0) with values (uidPredicateGroupID = 29EF7779-9DF3-42FC-85CE-501C887EEBE8 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 629) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:1116653:27) with values (uidPredicateGroupID = 736A6BBD-74A5-4B20-90D0-35A2FC68B760 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 627) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:1179220:13) with values (uidPredicateGroupID = 81700FA4-C744-4ACB-945B-61803334BD4F and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 624) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'EqualsPredicates.IX_EqualsPredicates' (ID 581577110) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:1290745:7) with values (uidPredicateGroupID = 54A9D334-4CA5-4EC8-B2BE-8A5D020D70C1 and uidPropID = B8F0EBDC-7D03-47E8-AA27-B79EA3CACA6B and vtValue = CacheRefresh  and ? = 626) points to the data row identified by ().                    
Server: Msg 8951, Level 16, State 1, Line 1                    
Table error: Table 'PredicateGroup' (ID 917578307). Missing or invalid key in index 'IX_PredicateGroup' (ID 2) for the row:                    
Server: Msg 8955, Level 16, State 1, Line 1                    
Data row (1:164805:72) identified by (RID = (1:164805:72) uidPredicateANDGroupID = 29EF7779-9DF3-42FC-85CE-501C887EEBE8 and nNumFirstPassPredicates = 1) has index values (uidPredicateORGroupID = 88FB6060-A981-4227-834A-CF94E8F69B37 and fIsDistributionList = 0 and uidPredicateANDGroupID = 29EF7779-9DF3-42FC-85CE-501C887EEBE8 and uidPredicateANDGroupID = 29EF7779-9DF3-42FC-85CE-501C887EEBE8 and ...                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'PredicateGroup.IX_PredicateGroup' (ID 917578307) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:241422:72) with values (uidPredicateORGroupID = F40EF460-59F9-42D2-BF28-527A99F69A98 and fIsDistributionList = 0 and uidPredicateANDGroupID = 0DBAA653-3C36-4E1C-A336-193003D41E90 and uidPredicateANDGroupID = 0DBAA653-3C36-4E1C-A336-193003D41E90 and nNumFirstPassPredicates = 1) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'PredicateGroup.IX_PredicateGroup' (ID 917578307) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:433240:48) with values (uidPredicateORGroupID = A06412EB-6ACD-4696-BE35-FAAFE12FD904 and fIsDistributionList = 0 and uidPredicateANDGroupID = 81700FA4-C744-4ACB-945B-61803334BD4F and uidPredicateANDGroupID = 81700FA4-C744-4ACB-945B-61803334BD4F and nNumFirstPassPredicates = 1) points to the data row identified by ().                    
Server: Msg 8952, Level 16, State 1, Line 1                    
Table error: Database 'BizTalkMsgBoxDb', index 'PredicateGroup.IX_PredicateGroup' (ID 917578307) (index ID 2). Extra or invalid key for the keys:                    
Server: Msg 8956, Level 16, State 1, Line 1                    
Index row (1:675058:18) with values (uidPredicateORGroupID = DF619098-E25D-4C75-BF85-054D76396C27 and fIsDistributionList = 0 and uidPredicateANDGroupID = 4ADF185B-8E72-431A-81DD-9EF2F9052E16 and uidPredicateANDGroupID = 4ADF185B-8E72-431A-81DD-9EF2F9052E16 and nNumFirstPassPredicates = 1) points to the data row identified by ().                    

As an after effect, what I am seeing is that the MessageBox size has grown significantly and have seen the log files go up just like that without coming down to its normal size even though no activity is happening.

Appreciate any help on this?

Thanks Sajas

sql-server-2000biztalk
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered

Are the backups still being created? if the size is increasing it sounds like the transaction log backups are not running, or they are not able to clear the transaction log (because a transaction is still "running")

I presume you have a good backup in case things go from bad to worse.

I would reboot the server for good measure (which should also checkpoint any stuck transaction)

Then re-run CHECKDB

If you get the same errors I would REINDEX the tables indicated (EqualsPredicates and PredicateGroup) and then try CHECKDB again to see what is still showing.

Might also be caused by an underlying disk or memory issue

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.