Introduction

A new feature in BizTalk Server 2010 is a new SQL job called Monitor BizTalk Server. This job and other database jobs are created when you configure a BizTalk group.

Figure 1. BizTalk Server Database Jobs visible under SQL Server Agent Jobs.

Monitor BizTalk Server Job

The Monitor BizTalk Server job scans for issues, but does not fix them for you! It consist of two steps. First step is the execution of the [btsmon_Inconsistancies] stored-procedure. This procedure will on its turn call other procedures in the MessageBox, Management and Tracking database to collect error data (like messages without references). The second step is calling the procedure [btsmon_GenerateErrorString] in Management database, which involves a query of two tables [btsmon_Inconsistancies] and [btsmon_Issues].

Figure 2. Job Properties of Monitor BizTalk Server Job - Steps.

When you query the table [btsmon_Issues] of BizTalk Management Database (BizTalkMgmtDb) you will see the description of the issues that can be detected:

Figure 3. Job Properties of Monitor BizTalk Server Job - Steps.

If job encounters any issues if will fail and return a string containing number of issues found (i.e. result of second step). This string will by default be logged to the event viewer and is also visible in the Job History.

Figure 4. Monitor BizTalk Server Job History (click to enlarge).

Custom View

Although this job does not fix any issues you can leverage this job by creating a monitoring solution or altering mechanism for administrators so they can act on any issues at hand. This does mean customizations on your part. Either by leveraging the alert or notification capabilities of SQL Server Jobs or by for instance creating a view that shows any issues. You can create a custom view using the following TSQL code:

CREATE VIEW


[dbo].[bts_Monitor_Job_Issues]

AS

SELECT Inconsistancy.DBServer AS [Server Name

], Inconsistancy.DBName

AS

Database


Name

], Inconsistancy.nProblemCode AS [Problem Code], Issue.nvcProblemDescription AS [Description] FROM btsmon_Inconsistancies Inconsistancy INNER JOIN

[dbo].[btsmon_Issues] Issue

ON


Inconsistancy.nProblemCode = Issue.nProblemCode

WHERE Inconsistancy.nCount > 0

GO

This view will show any issues resulting from the execution of the Monitor BizTalk Server Job.

Figure 5. Result from executing query on the custom view.

Considerations

There are a few things to consider when running this job:

According to the Microsoft documentation this job is computational intensive and therefor scheduled once a week by default. Microsoft recommends to run this job during low traffic or downtime.
As stated in the previous point the job is scheduled once a week by default. You can however consider to run the job more often. Once a day during low traffic.
The job will not detect if issues have already been reported. When the job is run a subsequent time and the issues have not been fixed they will be reported again.
The job will only detect issue but not fix them. You should configure an alert or notification on the job in case the job fails due to errors.
In case you run into issues with the job you might need to apply a cumulative update, see for instance kb2452550.

Resolving detected issues

In case issues are detected by the Monitor BizTalk Server Job you will need to take action to resolve them. The BizTalk terminator tool can aid you in resolving any of the issues detected by this job. See the blog post created by One Blog for BizTalk Engineers: Using BizTalk Terminator to resolve issues identified by BizTalk MsgBoxViewer and the BizTalk 2010 Monitor BizTalk Server Job. The terminator tool can be obtained through the Microsoft Download Center: BizTalk Terminator.

Note: When using the terminator tool. Backup BizTalk Databases Stop all host instances and Stop SQL Agent. Read the full description of each task before running it!