Using BizTalk Terminator to resolve issues identified by BizTalk Health Monitor (BHM), MsgBoxViewer, and the BizTalk 2010 Monitor BizTalk Server Job
One of the primary purposes behind creating the BizTalk Terminator tool was to allow users the ability to easily resolve many of the common database integrity issues identified by the BizTalk MsgBoxViewer tool. Also, with BizTalk 2010, the BizTalk product group added a new SQL Agent Job to the product called Monitor BizTalk Server that identifies many of these same database integrity issues. The purpose of this post is to illustrate how BizTalk Terminator can be used to resolve database integrity issues identified by MBV or the BTS2010 Monitor BizTalk Server job.
Update: MBV is no longer being updated and has been replaced by BizTalk Health Monitor:
- BHM was originally shipped with BizTalk 2013 R2 and continues to be updated at https://www.microsoft.com/en-us/download/details.aspx?id=43716
- The last update to MBV was in July 2014. To make sure you are using the latest MBV repository with the latest rules/queries, be sure to move to BHM
- Starting with BHM v3, Terminator has been integrated into BHM so you have one tool that can provide MBV and Terminator functionality
- See here for a walkthrough of BHM v3 functionality - including Terminator integration
- See the BHM blog at https://blogs.msdn.com/b/biztalkhealthmonitor for more information on BHM
Looking for info on where to download Terminator? Go here Terminator Not Cleaning Up Caching Items? Go here |
Resolving issues identified by the BTS2010 Monitor BizTalk Server Job
One of the new features introduced in BizTalk 2010 is the Monitor BizTalk Server SQL Agent job. This job was added to the product to provide an out-of-the-box way to identify database integrity issues that we previously had to use MBV or manual SQL scripts to identify. If this job identifies a database integrity issue, the job itself will fail with a custom error message that describes the problem that was identified. A database admin or a monitoring tool like SCOM can take a look at SQL Agent to identify when a problem has occurred. See the BizTalk Server Monitoring section at https://msdn.microsoft.com/en-us/library/aa577454 (BTS.70).aspx for more information about this job.
Terminator provides the easiest way to resolve most of these issues once they have occurred. Use the steps below to resolve issues identified by the Monitor BizTalk Server Job.
1. Before running Terminator, always make sure you have a BizTalk Backup of your databases, all the BTS hosts have been stopped, BTS SQL Agent jobs have been disabled.
2. Use Table 1 below to determine the Terminator View and Resolution Tasks as well as the Terminator Task Category for the tasks associated with the issue identified by the BizTalk Monitor Job
3. Open Terminator to enter the initial screen.
4. Specify the SQL server and database information for the BizTalk Management Database.
5. Check the checkboxes to acknowledge that you have completed step #1 above and click Connect.
6. Once connected, click the appropriate button depending on the Terminator Task Category
7. Select the appropriate task from the list, click the Parameters tab and populate any parameter that needs to be set and click the Execute button.
Table 1 provides a summary of all the common database integrity issues identified by the Monitor job and the associated task in Terminator that is used to resolve or view the issue. Note that some issues do not have any resolution options and will require troubleshooting by Microsoft Support.
| Issue Identified by Monitor BizTalk Server Job | Terminator Resolution Task | Terminator View Task |
| Messages without any references | Repair Refcounts for All Messages
(in Manage task category) |
No view task - rely on the Monitor job to identify these |
| Messages without reference counts | Repair Refcounts for All Messages
(in Manage task category) |
View Count of Messages Without RefCounts |
| Messages with reference count less than 0 | Repair Refcounts for All Messages
(in Manage task category) |
No view task - rely on the Monitor job to identify these |
| Message references without spool rows | Run the View task first. If the InstanceExists value is true, use the uidInstanceID value to find the instance in Admin Console and terminate it. If the InstanceExists value is false, that means this message ref is missing spool row AND instances row. In that case you can run the DELETE Orphaned Messages Terminator task (in Delete task category) | View Message References Without Spool Rows. |
| Message references without instances | DELETE Orphaned Messages in All Hosts
DELETE Orphaned Messages in Specific Host (both in Delete task category) |
View Count of Orphaned Messages by Host |
| Instance state without instances | No resolution task - contact MS support | No view task - rely on the Monitor job or MBV to identify these |
| Instance subscriptions without corresponding instances | DELETE Orphaned Instance Subscriptions
(in Delete task category) |
View Count of Orphaned Instance Subscriptions |
| Orphaned DTA service instances | Repair Orphaned DTA Service Instances
(in Manage task category) |
View Count of Orphaned DTA Service Instances |
| Orphaned DTA service instance exceptions | DELETE Orphaned DTA Service Instance Exceptions
(in Delete task category) |
View Count of Orphaned DTA Service Instance Exceptions |
| TDDS is not running on any host instance when global tracking option is enabled | No resolution task within terminator - BizTalk Administrator needs to make sure one of the hosts has Tracking enabled and there is a running host instance for that host | No view task - rely on the Monitor job or MBV to identify these |
Table 1. BTS Monitor job-identified Issues and Associated Terminator Tasks
Resolving issues identified by BizTalk Health Monitor (BHM) or MsgBoxViewer
First, MBV is always being improved and updated so make sure you are always running the latest version. Below are some relevant MBV links:
- MBV Blog - https://blogs.technet.com/jpierauc/
- MBV FAQ - https://blogs.technet.com/b/jpierauc/archive/2008/07/22/msgboxviewer-mbv-q-a.aspx
- MBV Download location - https://blogs.technet.com/jpierauc/pages/msgboxviewer.asp x
As mentioned at the top of this page, MBV is no longer being updated and has been replaced by BizTalk Health Monitor.
There are 3 ways to use Terminator to resolve issues identified by BHM/MBV. This document will outline the options in detail:
- BHM Integration - BHM has Terminator built-in plus auto-proposes Terminator tasks using the "From Latest BHM Report" task category. See here for a walkthrough.
- MBV Integration – With a minimum of MBV version 10.13, MBV will output MBVCleanupScripts.xml which Terminator can consume to automatically provide the user with a list of Tasks that can be used to resolve identified issues. Not all issues identified by MBV and addressable with Terminator are supported by MBV integration.
- Manual Task Selection – The user can look at the BHM or MBV output and manually select the appropriate tasks in Terminator.
Table 2 below provides a summary of all the common database integrity issues identified by BHM/MBV and the associated task in Terminator that used to resolve or view the issue. Note that some issues require manual integration and some do not have any integration options and will require troubleshooting by Microsoft Support.
Issue Identified by BHM/MBV |
Resolution Options |
Terminator Resolution Task |
Terminator View Task |
Root Cause |
BHM/MBV Integration or Manual Task Selection |
DELETE Orphaned RFRs in all Hosts DELETE Orphaned RFRs in Specific Host (both in Delete task category) |
View Count of RFRs By Host |
This is due to a known bug and there is a hotfix available. See KB 941690 for details. |
|
BHM/MBV Integration or Manual Task Selection |
Terminate Caching Instances (in Delete task category) |
View Count of Cache Messages in All Host Queues View Count of Cache Instances in All Hosts |
This is due to a known bug and there is a hotfix available. See KBs 944426 & 936536 for details. |
|
BHM/MBV Integration or Manual Task Selection |
Repair Orphaned DTA Service Instances (in Manage task category) |
View Count of Orphaned DTA Service Instances |
This is due to a known bug and there is a hotfix available. See KB 978796 for details. This can also be due to running a cleanup script against trackingdata_x_x or instances table. |
|
BHM/MBV Integration or Manual Task Selection |
DELETE Orphaned DTA Service Instance Exceptions (in Delete task category) |
View Count of Orphaned DTA Service Instance Exceptions |
No known root cause |
|
Message Refs w/out Spool rows |
None |
Run the View task first. If the InstanceExists value is true, use the uidInstanceID value to find the instance in Admin Console and terminate it. If the InstanceExists value is false, that means this message ref is missing spool row AND instances row. In that case you can run the DELETE Orphaned Messages Terminator task (in Delete task category) |
View Message References Without Spool Rows |
No known root cause |
BHM/MBV Integration or Manual Task Selection |
Repair Refcounts for All Messages (in Manage task category) |
View Count of Messages Without RefCounts |
This is due to an issue in BTS reference counting logic. |
|
Messages with RefCount = -1 (or less) |
Manual Task Selection |
Repair Refcounts for All Messages (in Manage task category) |
No view task. |
This is due to an issue in BTS reference counting logic. |
BHM/MBV Integration or Manual Task Selection |
DELETE Orphaned Messages in All Hosts DELETE Orphaned Messages in Specific Host (both in Delete task category) |
View Count of Orphaned Messages by Host |
Only known root cause are Orphaned RFRs but we have seen other types of message refs w/o instances and there is no know root cause for those |
|
Subscriptions w/out Instances |
Manual Task Selection |
DELETE Orphaned Instance Subscriptions (in Delete task category) |
View Count of Orphaned Instance Subscriptions |
No known root cause |
InstanceState w/out Instances |
None |
No cleanup task – contact MS support. |
No view task. |
No known root cause |
BHM/MBV Integration or Manual Task Selection |
Repair Control Messages (in Manage task category) |
View Missing Control Messages |
No known root cause |
|
Manual Task Selection Only |
Resume Instances or Terminate Instances and select Host, Class, and Status as appropriate. (Resume in Manage task category and Terminate in Delete task category) |
Count Instance (and Save Messages) |
Could be due to various causes that lead instances to get suspended. BTS Admin will need to look at the error message associated with the suspended items to investigate root cause. |
|
BHM/MBV Integration or Manual Task Selection |
PURGE Marklog Table (in Delete task category) |
View Table Row Count (No SQL Statistics) |
Due BTS backup job’s backup on mark logic. Mark is created across all DBs involved in backup so restores can be done in a transactionally consistent state. |
Table 2. BHM/MBV-identified Issues and Associated Terminator Tasks
BHM Integration
BHM has Terminator functionality built-in so you don't need the standalone Terminator tool for this. BHM auto-proposes Terminator tasks using the "From Latest BHM Report" task category. See here for a walkthrough.
MBV Integration
This feature allows the user to resolve many of the common database integrity issues MBV identifies without having to analyze the MBV output or determine which task in Terminator is associated with which issue. MBV 10.13 or above is required for this functionality and will produce an MBVCleanupScripts.xml file used by Terminator for MBV integration.
How to use Terminator's MBV mode for MBV Integration:
1. Run MBV and verify that an MBVCleanupScripts.xml file has been created in the output folder.
2. Before running Terminator, always make sure you have a BizTalk Backup of your databases, all the BTS hosts have been stopped, BTS SQL Agent jobs have been disabled.
3. Open Terminator to enter the initial screen (Figure 1).
4. Specify the SQL server and database information for the BizTalk Management Database.
5. Check the checkboxes to acknowledge that you have completed step #2 above and click Connect.
6. Once connected, click the Import MBV button (Figure 2) and browse to the MBVCleanupScripts.xml file created in step #1.
7. Notice that the dropdown list says "Select a MBV task from this drop down" (Figure 3) and clicking the dropdown provides a list of all tasks that have been selected to resolve all the issues that MBV identified (and support MBV integration mode).
8. Execute each task from the list to resolve the issues identified by MBV. Note that the Parameters tab must be clicked to enable the Execute button. Make sure all params are populated correctly before executing a task. The user can re-run MBV or use the Terminator View tasks mentioned in Table 2 to verify that the issues have been resolved.
 Figure 1. Initial Terminator Screen |
 Figure 2. Import MBV |
 Figure 3. MBV-identified task list |
Important considerations for MBV Integration:
· MBV only creates an MBVCleanupScripts.xml file if it identifies an issue that is addressable with Terminator using MBV Integration.
· MBVCleanupScripts.xml will not be created if MBV only identifies issues that require manual task selection to resolve and do not support MBV Integration.
· Once the MBV file has been imported, use the MBV Task Category button and other Task Category buttons to go in and out of MBV mode
· There are currently 8 Terminator tasks that can be pre-selected by MBV Integration:
1. DELETE Orphaned RFRs in all Hosts
2. Terminate Caching Instances
3. Repair Orphaned DTA Instances
4. DELETE Orphaned DTA ServiceInstance Exceptions
5. Repair Refcounts for All Messages
6. DELETE Orphaned Messages in All Hosts
7. Repair Control Messages
8. PURGE Marklog Table
Manual Task Selection
In some cases, the user prefers to analyze the BHM/MBV output and select the Terminator tasks in a manual fashion.
Manual Task Selection Steps:
1. Before running Terminator, always make sure you have a BizTalk Backup of your databases, all the BTS hosts have been stopped, BTS SQL Agent jobs have been disabled.
2. Use the below sections of this document to identify an issue mentioned in the Warnings and Summary Report section of the MBV output html and note down the Terminator View and Resolution Tasks as well as the Terminator Task Category.
3. Open Terminator to enter the initial screen.
4. Specify the SQL server and database information for the BizTalk Management Database.
5. Check the checkboxes to acknowledge that you have completed step #1 above and click Connect.
6. Once connected, click the appropriate button depending on the Terminator Task Category
7. Select the appropriate task from the list, click the Parameters tab and populate any parameter that needs to be set and click the Execute button.
Orphaned RFRs in Critical Warnings section of Warning and Summary Report:
|
| MsgBox Database Integrity | <> | ||
| Total Orphaned 'Routing Failures' messages | 10991 (Possible Known Issue in BizTalk 2006) !! | |
KB 941690 Get More Info on 'TERMINATOR' Tool to clean or repair this issue , |
Click Query Report button to see further details:
MsgBox Dbs : MsgBox Integrity Details (SQL Query, 4 Rules) - Messages count with no associated instances grouped by classID |
| MsgBox Db | HostName | uidClassID | count |
| SERVER – BizTalkMsgBoxDb | My32BitHost | eaf8eea9- 366a-4cde-8dd3-57a4c39bf8e5 (Routing Failures Class) | 10991 |
| SERVER - BizTalkMsgBoxDb | My32BitHost | 59f295b0- 3123-416e-966b-a2c6d65ff8e6 (Messaging Class) | 10020 |
| SERVER – BizTalkMsgBoxDb | My32BitHost | bb3a1470- f5c4-47c3-b71f-eaabc260fbd0 (Caching Class) | 10033 |
| 3 Rows |
Terminator View Task: · View Count of RFRs By Host (in View category) Terminator Resolution Tasks: · DELETE Orphaned RFRs in all Hosts (in Delete category) · DELETE Orphaned RFRs in specific Host (in Delete category)
|
| Orphaned Cache Instancesin Critical Warnings section of Warning and Summary Report:
|
| Other MsgBox Checks | <> | ||
| Total active 'Cache msg' for MsgBox MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb" on SERVER | 5009 (they do NOT appear in HAT or BTS MMC and large number can impact MsgBox perfs) !! | |
kb 944426 - Orphaned cache instances may be built in the Instances table You experience performance issues with BizTalk Server 2006 and throttling messages are logged in the performance log file , |
Click Query Report button to see further details:
MsgBox Dbs : Active Cached Msg Ref in Spool (SQL Query, 2 Rules) - MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb" on SERVER |
| Host Q | count |
| BizTalkServerApplicationQ | 0 |
| My32BitHostQ | 5009 |
| CreateTestHostQ | 0 |
| TestJoBailQ | 0 |
| 4 Rows |
Terminator View Task: · View Count of Cache Messages in all Hosts Queues (in View category). · View Count of Cache Instances in all Hosts (in View category). Note that while MBV shows the number of orphaned cache messages, this Terminator task shows the number of cache instances. One cache instances can have thousands of cache messages associated with it.
Terminator Resolution Task: · Terminate Caching Instances (in Delete category). Note that while MBV shows the number of orphaned cache messages, Terminator terminates cache instances. One cache instances can have thousands of cache messages associated with it.
|
| Orphaned DTA Service Instances in Critical Warnings section of Warning and Summary Report:
|
| DTA Tables | <> | ||
| DTA Orphaned Instances (Incompleted Instances in DTA but not in Msgbox) | 10989 (Large number can impact DTA Size and so perfs) !! | |
Get More Info on 'TERMINATOR' Tool to clean or repair this issue |
Click Query Report button to see further details:
DTA Db : Orphaned Svc Instances (SQL Query, 2 Rules) |
| Instances Start Date | Orphan Instances Count |
| 01/15/2009 | 2 |
| 02/24/2009 | 1566 |
| 04/08/2009 | 9416 |
| 10/06/2008 | 1 |
| 10/07/2008 | 3 |
| 11/05/2008 | 1 |
| 6 Rows |
Terminator View Task: · View Count of Orphaned DTA Service Instances (in View category) Terminator Resolution Task: · Repair Orphaned DTA Service Instances (in Manage category)
|
| Orphaned DTA Service Instance Exceptionsin Non Critical Warnings section of Warning and Summary Report:
|
| Other Checks | <> | ||
| "ServiceInstanceExceptions" DTA table | Some entries do not have a instance in dta_ServiceInstances : 10100 ! | |
Get More Info on 'TERMINATOR' Tool to clean or repair this issue |
Click Query Report button to see further details:
DTA Db : Orphaned serviceInstanceExceptions (SQL Query, 1 Rule) |
| Count |
| 10100 |
| 1 Rows |
Terminator View Task: · View Count of Orphaned DTA Service Instance Exceptions (in View category) Terminator Resolution Task: · DELETE Orphaned DTA Service Instance Exceptions (in Delete category)
|
Messages w/out RefCountsin Non Critical Warnings section of Warning and Summary Report:
|
| MsgBox Database Integrity | <> | ||
| Total Messages without Ref Count | 11370 (Large number can impact MsgBox Dbs size and so perfs) ! | |
Get More Info on 'TERMINATOR' Tool to clean or repair this issue |
Click Query Report button to see further details:
|
| MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb" on SERVER | Messages w/out RefCounts | 11370 |
Terminator View Task: · View Count of Messages Without RefCounts (in View category)
Terminator Resolution Task: · Repair Refcounts for All Messages (in Manage category)
|
| Message Refs w/out Instances in Non Critical Warnings section of Warning and Summary Report:
|
| MsgBox Database Integrity | <> | ||
| Total Orphaned Messages not belonging to MSMQT or Routing Failure Svc Class | 20053 (Large number can impact MsgBox Dbs size and so perfs) ! | |
Get More Info on 'TERMINATOR' Tool to clean or repair this issue |
Click Query Report button to see further details. There are 20053 messages without instances not including the 10991 orphaned RFRs:
MsgBox Dbs : MsgBox Integrity Details (SQL Query, 4 Rules) - Messages count with no associated instances grouped by classID |
| MsgBox Db | HostName | uidClassID | count |
| SERVER - BizTalkMsgBoxDb | My32BitHost | eaf8eea9- 366a-4cde-8dd3-57a4c39bf8e5 (Routing Failures Class) | 10991 |
| SERVER – BizTalkMsgBoxDb | My32BitHost | 59f295b0- 3123-416e-966b-a2c6d65ff8e6 (Messaging Class) | 10020 |
| SERVER – BizTalkMsgBoxDb | My32BitHost | bb3a1470- f5c4-47c3-b71f-eaabc260fbd0 (Caching Class) | 10033 |
Go to MsgBox Dbs : MsgBox Integrity (SQL Query, 3 Rules) to see more details. This a total count including orphaned RFRs and orphaned MSMQT messages:
|
| MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb3" on SERVER | Message Refs w/out Instances | 31044 |
Terminator View Task: · View Count of Orphaned Messages by Host (in View category) Terminator Resolution Task: · DELETE Orphaned Messages in All Hosts (in Delete category). Note that this task will also clean up orphaned RFRs and orphaned MSMQT messages. · DELETE Orphaned Messages in Specific Host (in Delete category). Note that this task will also clean up orphaned RFRs and orphaned MSMQT messages.
|
| Missing Control Messagesin Critical Warnings section of Warning and Summary Report:
|
| MsgBox Database Integrity | <> | ||
| 'Control Messages' Check | Missing Restart Msg in Spool table (can prevent some svc instances to be resumed, terminated or suspended) !! | Get More Info on 'TERMINATOR' Tool to clean or repair this issue | |
| 'Control Messages' Check | Missing Suspend Msg in Spool table (can prevent some svc instances to be resumed, terminated or suspended) !! | Get More Info on 'TERMINATOR' Tool to clean or repair this issue | |
| 'Control Messages' Check | Missing Terminate Msg in Spool table (can prevent some svc instances to be resumed, terminated or suspended) !! | Get More Info on 'TERMINATOR' Tool to clean or repair this issue | |
| 'Control Messages' Check | Missing ResumeInDebugMode Msg in Spool table (can prevent some svc instances to be resumed, terminated or suspended) !! | Get More Info on 'TERMINATOR' Tool to clean or repair this issue |
Terminator View Task: ·View Missing Control Messages (in View category)
Terminator Resolution Task: · Repair Control Messages (in Manage category).
|
| Large Number of Suspended Messagesin Non Critical Warnings section of Warning and Summary Report:
|
| MsgBox Queues | <> | ||
| Total Suspended Messages for BizTalkMsgBoxDb3 | 48926 - Large number can impact MsgBox db Perfs ! | |
BizTalk Server : Monitoring and Troubleshooting BizTalk Operation Guide - p388 , |
Click Query Report button to see further details: MsgBox Dbs : BizTalk Queues (SQL Query, 4 Rules) - MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb3" on SERVER |
| Queue | Count | Resumables |
| My32BitHostQ | 10224 | |
| My32BitHostQ_Suspended | 48926 | 48926 |
| InstanceStateMessageReferences_My32BitHost | 0 | |
| TestJoBailQ | 0 | |
| TestJoBailQ_Suspended | 0 | 0 |
| InstanceStateMessageReferences_TestJoBail | 0 | |
| CreateTestHostQ | 0 | |
| CreateTestHostQ_Suspended | 0 | 0 |
| InstanceStateMessageReferences_CreateTestHost | 0 | |
| BizTalkServerApplicationQ | 0 | |
| BizTalkServerApplicationQ_Suspended | 0 | 0 |
| InstanceStateMessageReferences_BizTalkServerApplication | 0 | |
| 12 Rows |
Terminator View Task: · Count Instances (and Save Messages) (in View category)
Terminator Resolution Task: · Resume Instances (in Manage category). User must verify suspended items need to be resumed before executing. · Terminate Instances (in Delete category). User must verify suspended items need to be terminated before executing.
|
| Large MarkLog Tablein Non Critical Warnings section of Warning and Summary Report:
|
| MsgBox Tables | <> | ||
| MarkLog Table Rows in BizTalkMsgBoxDb3 | 150000 starts to be a large number - Known issue caused by BackupJob ! | |
Contact CSS to get 'TERMINATOR' Tool to clean or repair this |
Terminator View Task: ·View Table Row Count (No SQL Statistics) (in View category). Find MarkLog table in the output list. Terminator Resolution Task: ·PURGE Marklog Table (in View category)
|
Comments
- Anonymous
January 13, 2010
Hi,I have BizTalk 2009 + ESB Toolkit V2. When I try to run the terminator it complains about a version incompatibility with the EnterpriseLibrary. Can you update Terminator to use the same version as the ESB so it can be used on BTS2009. - Anonymous
February 19, 2010
@MikeGBUK:We are actually doing away with using EnterpriseLibrary altogether within Terminator and should have that version published shortly. This should prevent any conflict with ESB toolkit V2. - Anonymous
March 04, 2010
FYI, we just published the latest build of Terminator that removes all EnterpriseLibrary references and should have no conflict with ESB V2. - Anonymous
October 19, 2010
The comment has been removed - Anonymous
October 21, 2010
Hello,I've also problems: I'm not able to load any script. I've checked .net Framwork and installed PowerShell 2.0. But Terminator complains with unhandled exeption see:http://textbin.com/0166r(german system)Whats wrong or did I miss something?best regards - Anonymous
November 05, 2010
HiSeems a very efficient tool but i'm unable to get it run : I always get "unable to find an entry point named RegGetValue in DLL "Advapi32.dll"i m working on XP SP3 workstationthanks per advance for your help - Anonymous
November 08, 2010
The comment has been removed - Anonymous
November 10, 2010
The comment has been removed - Anonymous
November 10, 2010
@Nhald, it looks like you may not have filled out the Parameters in the Parameters tab before executing the task. - Anonymous
November 10, 2010
@Mathias and @Phil.Can you post an excerpt from your Terminator trace (c:TEMPbiztalkterminator.log) so I can look further into your issues. - Anonymous
January 09, 2011
with up-to-date version there is no more problem, thanks! - Anonymous
November 19, 2016
The comment has been removed