Export (0) Print
Expand All

BizTalk Server 2004: Monitoring and Troubleshooting

Harold Perry

Microsoft Corporation

June 2005

Applies to: Microsoft BizTalk Server 2004

Summary: This document describes tools and techniques for monitoring a Microsoft BizTalk Server system on a recurring basis. It also provides general troubleshooting techniques for when you find an anomaly, error, or other destructive behavior in the system. (21 printed pages)

This document describes tools and techniques for monitoring a Microsoft BizTalk Server system on a recurring basis. It also provides general troubleshooting techniques for when you find an anomaly, error, or other destructive behavior in the system.

You should be familiar with basic BizTalk Server behavior and functionality, Microsoft SQL Server, and basic Microsoft Windows monitoring tools such as Event Viewer. The user who performs the steps in this document usually needs BizTalk Administrator privileges.

The goal of monitoring is to minimize the amount of time that an exception goes undetected and, therefore, unresolved. Optimally, you use monitoring to help detect a potential exception and guide you to take steps to avoid an exception.

BizTalk Server monitoring falls into three main categories: health monitoring, availability monitoring, and performance monitoring.

Health Monitoring

Health monitoring is concerned with how BizTalk Server and your solution are working at the application level. This is where your business processes live. The following table shows health-monitoring tools.

Table 1 Tools for health monitoring and troubleshooting

Tool Task

Event Viewer

Detect problems that occur during the processing of messages and orchestrations.

Health and Activity Tracking (HAT)

Query for messages and debug orchestrations.

BizTalk Administration console MMC snap-in

Interact with services and check on overall status of the system.

Business Activity Monitoring (BAM)

Develop solution-specific ways to monitor the performance of a solution's stages.

SQL Query Analyzer

Monitor suspended instances and database size to diagnose system problems.

Availability Monitoring

Availability monitoring answers the question "Is there anything preventing the system from running correctly?" These issues are almost exclusively system-level ones, such as availability of services and connections. For example, if an adapter is failing because the Enterprise Single Sign-On service is stopped, this is an availability issue. The following table shows availability-monitoring tools.

Table 2 Tools for availability monitoring and troubleshooting

Tool Task

Event Viewer

Look for adapter connection issues, stopped services, and so on.

BizTalk Administration console MMC snap-in

Interact with (start and stop) services and examine the overall status of the system.

Performance Monitoring

Performance monitoring answers the question, "How efficiently is the system performing its work?" This kind of monitoring focuses primarily on the load on physical resources like databases and disks. For example, if the CPU utilization is consistently at 90 to 100 percent and a backlog of messages is forming, this is a performance issue at the computer level. The following table shows performance-monitoring tools.

Table 3 Tools for performance monitoring and troubleshooting

Tool Task

SQL Query Analyzer

Monitor database size and content to diagnose system problems.

When monitoring BizTalk Server, you are looking for any unexpected or anomalous behavior. Monitoring can be either a manual process, as described in the following paragraph, or automatic, as with using Microsoft Operations Manager (MOM).

Monitoring BizTalk Server is typically a manual process performed as follows:

  1. You (the BizTalk administrator) either find or receive a notification of an Event Viewer message posted by BizTalk Server.
  2. You note the instance ID of the BizTalk message or orchestration instance.
  3. You open the Health and Activity Tracking (HAT) tool and perform a query to find the instance in question.
  4. You perform troubleshooting steps to correct the problem.

You typically monitor the BizTalk runtime, which is used by all subsystems in BizTalk Server. The BizTalk runtime is made up of the following objects:

  • Hosts
  • Host instances (Isolated and In-process)
  • Receive locations
  • Ports
  • Orchestrations
  • Adapters
  • MessageBox databases
  • Tracking database
  • Configuration/Management database

This section describes monitoring tools, required privileges, and recommended schedules. It also discusses common errors and troubleshooting techniques.

Building an absolute schedule for monitoring all BizTalk solutions is either impossible or suboptimal. However, there are guidelines for building schedules that are optimized for your own solutions.

Scheduling tips

Consider the following questions when you create your schedules:

  • How many messages does your system process in a specified period?
  • Does the system process in an ongoing manner?
  • Does the system process in batches at a specific time?
  • What is the average size of the messages your system processes?
  • Does your solution process the large messages?
  • How much time should it take to process the messages?
  • How much disk space is required for the messages?
  • How long are the messages in your system?
  • Are many of the messages long-running?
  • How many messages do you expect to be in the MessageBox database at any specified time?

Event Viewer

  • Scheduling. As often as possible
  • Required privileges. Local Administrator, although follow-up may require BizTalk Administrator privileges
  • Tools. To open Event Viewer, click Start, point to Programs, point to Administrative Tools, and then click Event Viewer.

Event Viewer is the key to monitoring the health of BizTalk Server. If anything goes wrong during system startup, operation, shutdown, or message processing, an event appears in the tool. Although this tool seems simple, it provides critical troubleshooting information.

Because Event Viewer is a Microsoft Management Console (MMC) snap-in, you can use any MMC console that has the Event Viewer snap-in, or you can create your own console by starting MMC and adding the snap-in. Conveniently, the BizTalk Administration console contains the Event Viewer snap-in.

When you use Event Viewer, look for events from the following sources:

  • BizTalk Server 2004
  • ENTSSO (Enterprise Single Sign-On)
  • MSSQLSERVER
  • MSSQLServerOLAPServices
  • SQLSERVERAGENT
  • MSDTC (Distributed Transaction Coordinator)
  • Windows SharePoint Services 2.0

The Event Viewer error events typically give you an idea of where to start troubleshooting. Problems generally fall into one of two categories:

  • BizTalk message problems
  • System problems

The only two sources that post message error events are BizTalk Server 2004 and XLANG. Any other errors that you find are system issues.

With BizTalk Server 2004 or XLANG error events, you often find several events from other sources clustered together. This is because failing systems have a cascading effect, with the result being that BizTalk Server suspends the instance. Consider this when determining whether you have a health issue or an availability issue.

BizTalk Server 2004 Errors

Symptom

You receive an error event with the source equal to "BizTalk Server 2004."

Possible cause

This error could be caused by one of the following:

  • System problem
  • BizTalk Server message or Messaging Engine problem

Solution

To troubleshoot BizTalk Server errors
  1. In Event Viewer, make a note of the message instance ID or service instance ID.

  2. Examine the contents, and possible errors, around the event to gather direction for your next steps.

    If the error appears to be caused by the BizTalk Server message or the Messaging Engine, you can query for the message in the Health and Activity Tracking (HAT) tool by using the following procedure:

    1. On the Reporting menu, click Find Message.
    2. In Event Viewer, find the message instance ID.
    3. In the results pane, right-click the message and select one of the following to determine what went wrong:
      Orchestration Debugger - if there was a problem in an orchestration
      Message Flow - if there was a problem with the message itself
  3. Use the ID from step 1 to retrieve the BizTalk Server message in HAT. For more information, see the "Health and Activity Tracking" section.

    After you resolve the problem, either resume or terminate the suspended BizTalk Server message.

ENTSSO (Enterprise Single Sign-On) Errors

Symptom

You receive an ENTSSO (Enterprise Single Sign-On) error.

Possible cause

In the BizTalk Server context, Enterprise Single Sign-On (SSO) is responsible for storing adapter configurations. Therefore, if you find ENTSSO errors in your event log, they probably indicate a problem with one of your adapter configurations or with the connection to the master secret server or SSO database.

Solution

If you have not already done so, back up your SSO master secret. Until you do this, SSO will continue to litter your event logs with error messages telling you that the master secret has not been backed up.

For more information, see the SSO content in the BizTalk Server documentation.

MSSQLSERVER (SQL Server) Errors

Symptom

You receive an MSSQLServer (SQL Server) error.

Possible cause

Because BizTalk Server depends on SQL Server, errors here are often critical, and you should deal with them as quickly as possible to avoid extended outages. Often the issues reported by SQL Server are for the following reasons:

  • Lack of sufficient privileges
  • Connections timing out
  • Databases growing too large

Solution

Verify that the correct privileges have been granted.

If connections have timed out, check your network for possible problems.

If your network connection is not the problem, make sure that your databases have not grown too large. When databases become too large, they can cause a connection to time out.

For more information about troubleshooting BizTalk Server databases, see the "Health and Activity Tracking" and "Working with the MessageBox" sections.

For more information about general troubleshooting of databases, see SQL Server Books Online.

MSSQLServerOLAPServices (SQL Analysis Services) Errors

Symptom

You receive an MSSQLServerOLAPServices (SQL Analysis Services) error.

Possible cause

Errors that you see in Analysis Services generally do not affect the real-time operations of a BizTalk Server system. However, if you leave these errors unattended, you will lose analysis data and Business Activity Monitoring (BAM) or BizTalk Tracking Analysis will not display accurate information. If you are using BAM for administrative tracking, these errors can prevent you from effectively troubleshooting problem areas in your system.

Solution

For information about general troubleshooting of Analysis Services, see SQL Server Analysis Services Books Online.

SQLSERVERAGENT (SQL Server Agent) Errors

Symptom

You receive a SQLSERVERAGENT (SQL Server Agent) error.

Possible cause

SQL Server Agent runs scheduled jobs to move data to the tracking databases, remove old data, and several other "clean-up" tasks. If these tasks are not performed, the databases become large and slow, and a backlog is formed. At this point, the system is in critical danger of never catching up or of failing completely.

Solution

If you see SQL Server Agent errors in Event Viewer, resolve them as quickly as possible.

For more information about troubleshooting the SQL Server database, see "Working with the MessageBox."

For more information about general troubleshooting of SQL Server Agent, see SQL Server Books Online.

MSDTC (Distributed Transaction Coordinator) Errors

Symptom

You receive an MSDTC (Distributed Transaction Coordinator) error.

Possible cause

The Distributed Transaction Coordinator (DTC) is responsible for handling transactional information across process boundaries, and, as such, is critical for BizTalk Server functionality. If the DTC is down for any reason, your BizTalk Server system can be rendered useless very quickly; therefore, pay very close attention to any alerts received from this service.

Solution

For more information about general troubleshooting of Distributed Transaction Coordinator, see SQL Server Books Online.

Windows SharePoint Services 2.0 Errors

Symptom

You receive a Windows SharePoint Services error.

Possible Cause

Because Business Activity Services (BAS) runs under Windows SharePoint Services version 2.0, you should be mindful of these errors.

Solution

For more information about general troubleshooting of Windows SharePoint Services, see the Windows SharePoint Services documentation.

Health and Activity Tracking

  • Scheduling. As required
  • Required privileges. BizTalk Administrator
  • Tools. To open the Health and Activity Tracking (HAT) tool, click Start, point to Programs, point to Microsoft BizTalk Server 2004, and then click Health and Activity Tracking.

The HAT tool queries the BizTalk Server system for the state of messages and orchestrations. This is typically the first troubleshooting tool you use when a message-level exception occurs. You often use HAT in response to a BizTalk Server event posted in Event Viewer.

You can use HAT to do the following:

  • Use the query builder to create your own queries.
  • Use the Orchestration Debugger to step through suspended orchestrations. This lets you resolve exceptions and replay completed orchestrations to examine the path taken by a message.
To set up and configure HAT
  1. In Event Viewer, make a note of the BizTalk Server message instance ID or the service instance ID.

  2. In HAT, on the Configuration menu, click either Pipelines or Orchestrations. Then select the events you want to track. At a minimum, you should track the following events:

    • Service Start and End
    • Message Send and Receive
  3. On the Operations menu, run a HAT query by clicking either Service Instance or Messages.

    The report of either of these queries shows you all the suspended messages and orchestrations in your BizTalk Server system.

    The Status column displays information about the current state of a specific instance. If one of the following states is displayed, you should take action immediately:

    • Suspended (resumable)
    • Suspended (non-resumable)
    • In breakpoint

    If you see the In breakpoint state, a breakpoint has been set at the class level of an orchestration. This means that if the computer is a production computer, every time that the orchestration is called, it stops where the breakpoint has been set and waits for you to open it in the debugger. As you can imagine, this dramatically affects performance.

BizTalk Administration Console and Snap-in

  • Scheduling. Daily
  • Required privileges. BizTalk Administrator
  • Tools. To open the BizTalk Administration console, click Start, point to Programs, point to Microsoft BizTalk Server 2004, and then click BizTalk Server Administration.

The BizTalk Administration console is the primary application for starting, stopping, configuring, and administering an operational BizTalk Server system. Also, when performing availability monitoring, the BizTalk Administration console provides a quick status of the following:

  • Hosts
  • Host instances
  • Receive locations
  • Send ports
  • Send port groups
  • Orchestrations
  • Servers
  • Adapters
  • Business Activity Monitoring Event Bus statistics

The Event Viewer snap-in is included in the BizTalk Administration console. This lets you move from an event in Event Viewer directly to the problem area in BizTalk Server.

For more information about using the BizTalk Administration console, see the BizTalk Server documentation.

Business Activity Monitoring

  • Scheduling. Business Activity Monitoring (BAM) schedules vary on a solution-by-solution basis. Because BAM processes data in an ongoing manner, you should work with business users and developers to create BAM views.
  • Required privileges. You must be a BizTalk Administrator to configure BAM; however, for monitoring only, you can customize privileges on a view-by-view basis.
  • Tools. For monitoring with BAM, you need a Web browser and the Office Web Components version 10.

BAM is the business-side complement to Health and Activity Tracking (HAT). Where HAT helps you analyze the health of your underlying system, BAM helps you analyze the more abstract business processes that exist in your solution.

At first, BAM appears to be a tool that only a business user would use; however, BAM also serves well as an administrative tool. For example, where a business user may want to see how many times a particular purchase order process has been started, an administrator can insert a simple measurement or dimension and use the tool to see how long the operation takes to complete. With this small adjustment, an administrator can now see and monitor the BizTalk solution, not just the system. Because business solutions vary greatly, you must work with your developer to make sure that the appropriate dimensions are in place.

The workflow for BAM typically follows these stages:

  1. The business user defines the dimensions and measurements in Microsoft Excel.
  2. You (the BizTalk administrator) add dimensions and measures to the Excel file before giving it to the developer. To better understand the orchestrations, work with the developer so that you can determine what dimensions and measures will help you monitor most effectively.
    Of particular interest with any orchestration are the points where the orchestration connects to another computer or process. Often these points either are or become bottlenecks for many systems. By using BAM to monitor the response times for these connections, you can see trends and proactively deal with issues before they become problems.
  3. The developer uses the Excel file and the Tracking Profile Editor to link the dimensions and measures to the solution's orchestrations and deploys the profile.
  4. You use the BAM Manager (bm.exe) to generate the OLAP cubes and views that the business user can see on the Business Activity Services (BAS) site.

For more information about BAM, dimensions, measurements, OLAP cubes, and so on, see the BizTalk Server documentation.

Microsoft Operations Manager and the BizTalk Server Management Pack

  • Scheduling. Schedules are built into MOM.
  • Required privileges. Privileges are specific to each management pack.
  • Tools. Microsoft Operations Manager (MOM)

One tool you should consider when you build out your monitoring system is Microsoft Operations Manager (MOM) and the downloadable BizTalk Server 2004 Management Pack. Although discussion of the intricacies of MOM and the management pack is beyond the scope of this document, some description is appropriate.

MOM helps simplify identification of issues, streamlines the process for determining the root cause of a problem, and makes it easier to quickly restore services and to prevent potential IT problems. You can set rules that analyze a set of monitoring data and prescribe actions to take when the rule criteria are met. The actions can be alerts, system remedies, or other actions.

MOM management packs are packs of rules that you import into MOM. These packs, which are designed and built by software developers, aggregate data like events in Event Viewer. After the data is aggregated, the rules more effectively recommend steps to resolve the problem. Each rule in a management pack has information from the developer regarding the recommended steps to take for resolution.

One additional small but important feature of MOM is that of event consolidation. Often a single error in a system causes a glut of event messages in Event Viewer. These events, though informative, can distract you from the real problem in your system. MOM, together with the management packs, consolidates these error messages into smaller, actionable chunks for more focused troubleshooting.

Both Microsoft SQL Server and Microsoft BizTalk Server have management packs that have many useful rules, which relieve you of the laborious tasks of repeated monitoring.

For more information about Microsoft Operations Manager, see the MOM Web site. This site also contains a link for downloading management packs.

The MessageBox refers to the collection of MessageBox databases that exist in a particular system. This may be one or more databases, but they function as a unit.

  • Scheduling. The schedule set for monitoring the MessageBox is largely based on the amount of load on your system. We recommend that you start monitoring weekly and adjust based on your experiences.
  • Privileges required. To work with the MessageBox tables, you must have the privileges to view the database tables.
  • Tools. To monitor the database, you use SQL Query Analyzer.

MessageBox Tables

This section discusses the database tables that provide information about the MessageBox. The following table lists these database tables.

Table 4 Database tables for analyzing the MessageBox

Logical name Table name

Work Queue

<Host Name>Queue

Suspended Queue

<Host Name>Queue_Suspended

State Queue

InstanceStateMessageReferences_<Host Name>

Instances

Instances

Spool

Spool

Work Queue table

The Work Queue table contains the list of messages that are pending delivery to a particular host. Generally, this table should not grow too large (it should contain fewer than 100,000 rows).

The number of rows in this table indicates the number of messages waiting to be processed. A large number means you have a backlog, which could be caused by one of the following reasons:

  • Stress
  • Having a service window
  • Delayed retries resulting from failures

You should resolve the situation appropriately. Examine the Event Viewer errors to help isolate the problem.

Suspended Queue table

The Suspended Queue table is, as the name implies, the table where BizTalk Server maintains the list of suspended message instances. Often, if you have a suspended message, it has an associated instance that is also suspended. The exception to this is the case of a transport adapter such as BizTalk Message Queuing Adapter (MSMQT), which allows for the message to be suspended while the instance is still active.

This table should not grow. If it does, something in your system is failing. Examine the Event Viewer errors to help isolate the problem.

State Queue table

The State Queue table holds the list of messages that have been processed by an instance but will be needed later. Currently, only orchestrations and MSMQT use this functionality. When an orchestration uses the State Queue, it is usually because the orchestration performed some operations on a message, persisted the message, and might need the message later. This is normal operation, and you should take this into account when determining correct sizing of the State Queue.

The MSMQT scenario is the most important scenario for monitoring purposes. Because MSMQT uses a reliable, ordered protocol, if one message submission by MSMQT does not receive an acknowledgment, MSMQT will not send the rest of the messages. This causes a backlog in MSMQT and, therefore, in this table.

If a backlog occurs, check to see if messages are going to your outgoing queue. You may have to use Network Monitor to check network traffic between the BizTalk Server computer and the Message Queuing (MSMQ) computers to determine why they are not returning acknowledgments.

Additionally, examine the adapter configuration to verify that it is set up correctly.

Instances table

The Instances table contains the list of all message instances in the BizTalk Server system, regardless of the host they belong to. This is the list you can see in the Service Instances view in HAT. How you monitor this table depends largely on your solution.

If you typically have lots of dehydrated orchestrations waiting on responses, the Instances table reflects that. Additionally, if this table continues to grow in an unexpected manner, it could indicate that you are not receiving responses.

Spool table

The Spool table holds all the messages in the system. As described in the previous paragraphs, each table holds lists of particular messages. Those lists are actually references to messages in the Spool table. This database should not grow too large. As this database grows, database operations throughout the system become slow because of the bottleneck around accessing messages in this table.

Usually, if there are problems with this database growing large, SQL Server Agent is not running. SQL Server Agent runs purge jobs every minute, by default. This is garbage collection for the messaging system. If this garbage collection does not occur, the Spool table continues to grow, resulting in a slow and unhealthy system.

SQL Scripts

The following scripts help you monitor your BizTalk MessageBox databases.

ms942197.Caution(en-US,BTS.10).gifCaution
Making modifications to the BizTalk Server SQL databases is a high-risk activity that should only be performed by a SQL Server expert.

Some of the concepts you should be familiar with include the following:

  • The four isolation levels
  • The difference between a clustered and non-clustered index
  • Knowledge of deadlocks

If you are unfamiliar with these concepts, ask your SQL Server administrator for help. Although the following procedures should not have any detrimental effects, you should have a recent backup of your database.

Guidelines

The scripts in this document are read-only. Under no circumstances should you write to or modify the database. Use the following syntax guidelines when performing dirty reads on the database (a "dirty read" occurs when a transaction reads data that has not yet been committed, and is still subject to change):

  • Always start off all T-SQL segments that access the BizTalk Server databases with the following lines:
    set nocount on
    set transaction isolation level read committed
    set deadlock_priority low
    
  • Always use the "WITH (NOLOCK)" hint when referencing a table. There is no reason to hold locks. A dirty read should always be good enough for these types of actions. Also, dirty reads do not change your databases.
  • Only use the SELECT action keyword. Never use the UPDATE, DELETE, or INSERT statements.
  • Do not add items like triggers, tables, stored procedures, views, or functions because these may have unforeseen results. For example, triggers can cause performance issues because of the way they scan the transaction log.
  • Do not change anything in the BizTalk Server databases. For example, do not change the indexing scheme, do not add ALTER statements or DROP statements. By changing your databases, you put your system into an unsupported and unsupportable state.
Exception

There is one exception to the "no ALTER or DROP" rule. The samples occasionally use temporary tables to make the results more compact and readable or to enumerate through all the hosts that you have created. In that case, you have to create a temporary table and drop it for correct functionality; otherwise, the script would not work if you ran it again.

Run all scripts first in a test system. If you accidentally drop one of the real tables, you might have to reinstall, and any live data might be lost.

SQL Script: Orchestration Instances Grouped by Status

This script gives statistical information about all orchestration instances in your system and groups them by status. This is similar to right-clicking an orchestration in the BizTalk Administration MMC snap-in and selecting properties. However, this script lets you programmatically roll up data across all orchestration types.

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET DEADLOCK_PRIORITY LOW

SELECT o.nvcName AS Orchestration, COUNT(*) as Count,

CASE i.nState

WHEN 1 THEN 'Ready To Run'

WHEN 2 THEN 'Active'

WHEN 4 THEN 'Suspended Resumable'

WHEN 8 THEN 'Dehydrated'

WHEN 16 THEN 'Completed With Discarded Messages'

WHEN 32 THEN 'Suspended Non-Resumable'

END as State

FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (NOLOCK)

JOIN [BizTalkMgmtDb]..[bts_orchestration] AS o WITH (NOLOCK) ON i.uidServiceID = o.uidGUID

GROUP BY o.nvcName, i.nState

Table 5 How to use the results of the orchestration instances script

Results Do this

If you have lots of suspended instances, the system is failing somewhere.

Proceed to HAT to determine why the instances are failing.

If the results show lots of dehydrated instances, it could indicate a problem with a back-end system that is not responding in a timely manner, or it could indicate that a host is not running.

First examine the BizTalk Administration console to make sure that your hosts are running.

If your hosts are running correctly, use HAT and the Orchestration Debugger to find where the bottleneck exists.

If you have lots of Ready To Run services, your server is down or is under load and trying to catch up to the backlog.

If your servers are running, examine the Performance Monitor on both your BizTalk Server and SQL Server for possible problems in CPU utilization, disk issues, or network status.

SQL Script: Host Queue Counts

This query returns the table size for the Work, Suspended, and State queues.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp (AppQueue nvarchar(256), QueueSize int)
declare @nvcAppName sysname

declare MyCursor CURSOR FAST_FORWARD FOR
SELECT nvcApplicationName FROM [BizTalkMsgboxDb]..[Applications] WITH (NOLOCK)

open MyCursor
FETCH NEXT FROM MyCursor INTO @nvcAppName

WHILE (@@FETCH_STATUS = 0)
BEGIN
    INSERT INTO #Temp
    exec ('SELECT ''' + @nvcAppName + 'Q'', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName +'Q] 
WITH (NOLOCK)')
    
INSERT INTO #Temp
exec ('SELECT ''' + @nvcAppName + 'Q_Suspended'', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName +'Q_Suspended] 
WITH (NOLOCK)')
   
INSERT INTO #Temp
exec ('SELECT ''InstanceStateMessageReferences_' + @nvcAppName + ''', COUNT(*) 
FROM [BizTalkMsgBoxDb]..[InstanceStateMessageReferences_' + @nvcAppName + '] 
WITH (NOLOCK)')
    
FETCH NEXT FROM MyCursor INTO @nvcAppName
END  
SELECT * FROM #Temp ORDER BY QueueSize DESC

close MyCursor
deallocate MyCursor
DROP TABLE #Temp

Table 6 How to use the results of the queue counts script

Results Do this

If the Suspended queue grows, this means that instances are failing and are being suspended.

See the "Event Viewer" and "Health and Activity Tracking" sections to determine what is causing the failure.

In any case, terminate or resume the suspended instances to avoid having extraneous data in the system.

If the Work queue grows, messages are starting to form a backlog in your system. Perhaps your services are off, you are overdriving your system, or failures have occurred and the messages are awaiting retries. If the Work queue continues to grow over an extended period of time, it can result in system failure.

Examine the Performance Monitor to see if the CPU is at or near 100 percent for extended periods. If it is, you are overdriving your system and may have to distribute the load by adding more CPUs.

Failing transports also commonly cause the Work queue to fill. Use Event Viewer and the BizTalk Administration console to deal with these.

If the State queue grows, it could be that you have lots of dehydrated or suspended orchestrations.

Check HAT to see whether you have lots of dehydrated or suspended orchestrations and why.

If you are using MSMQT, it may be blocking these orchestrations because it has not received an acknowledgment from an earlier message.

SQL Script: Number of State Messages Grouped by Instance

This script returns the number of state messages associated with each instance. This specific query is primarily useful for systems that use MSMQT. If you are using MSMQT, and your system is starting to form a backlog, a connection problem may exist. Use this query to detect a backlog early so that you can correct the problem and avoid larger problems later.

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp (AppName nvarchar(256), StateMessages int, InstanceID uniqueidentifier, ServiceClassID uniqueidentifier, ServiceState nvarchar(256))

declare @nvcAppName sysname

declare MyCursor CURSOR FAST_FORWARD FOR

SELECT nvcApplicationName FROM [BizTalkMsgBoxDb]..[Applications]

WITH (NOLOCK)

open MyCursor

FETCH NEXT FROM MyCursor INTO @nvcAppName

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT INTO #Temp

exec ('SELECT ''' + @nvcAppName + ''', COUNT(*), i.uidInstanceID, i.uidClassID, i.nvcUserState

FROM [BizTalkMsgBoxDb]..[InstanceStateMessageReferences_' + @nvcAppName + '] AS s WITH (NOLOCK)

JOIN [BizTalkMsgBoxDb]..[Instances] AS i

WITH (NOLOCK) ON s.uidInstanceID=i.uidInstanceID

GROUP BY i.uidInstanceID, i.uidClassID, i.nvcUserState')

FETCH NEXT FROM MyCursor INTO @nvcAppName

END

SELECT * FROM #Temp ORDER BY StateMessages DESC

close MyCursor

deallocate MyCursor

DROP TABLE #Temp

Table 7 How to use the results of the state messages script

Results Do this

If you have one of these problematic instances and the service class is MSMQT, you can view the UserState column to determine the name of the queue to which you are talking, and then see why the communication with that computer's queue is not working.

If this is an orchestration, it is possible that this behavior is expected. Your business logic determines why so many messages are going to the same instance.

SQL Script: Count of Active Messages for a Specific Send Port

This query gathers the number of messages in a specified Work queue for a specific send port broken down by primary and secondary transports. Additionally, the query reports the maximum number of retries for any send port.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp(MaxRetries int, Active int, SendPort nvarchar(256), IsPrimaryTransport int)

declare @nvcAppName sysname
declare MyCursor CURSOR FAST_FORWARD FOR

SELECT nvcApplicationName NameFROM [BizTalkMsgBoxDb]..[Applications] WITH (NOLOCK)
open MyCursor
FETCH NEXT FROM MyCursor INTO @nvcAppName

WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO #Temp
exec('SELECT MAX(q.nRetryCount) AS MaxRetries,
COUNT(*) AS Active,
sp.nvcName AS SendHandlersHostServiceName,
spt.bIsPrimary AS IsPrimaryTransport 
FROM [BizTalkMsgBoxDb]..[' + @nvcAppName + 'Q] as q 
WITH (NOLOCK) 
INNER LOOP JOIN [BizTalkMsgBoxDb]..[Subscription] as s 
WITH (NOLOCK) ON q.uidServiceID = s.uidServiceID 
AND s.uidPortID = q.uidPortID
INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport] AS sp 
WITH (NOLOCK) ON q.uidServiceID = sp.uidGUID
INNER LOOP JOIN [BizTalkMgmtDb]..[bts_sendport_transport]
AS spt 
WITH (NOLOCK) ON sp.nID = spt.nSendPortID 
AND spt.uidGUID = q.uidPortID
GROUP BY sp.nvcName, s.uidPortID, spt.bIsPrimary
ORDER BY SendHandlersHostServiceName ASC')

FETCH NEXT FROM MyCursor INTO @nvcAppName
END
SELECT * FROM #Temp ORDER BY Active DESC

close MyCursor
deallocate MyCursor
DROP TABLE #Temp

Table 8 How to use the results of the active messages script

Results Do this

If the retry counts are high, something in your system is failing.

– Or –

If lots of messages appear on the secondary transports, it means that your primary transports are failing.

Often these types of failures are caused by connection failures between the send port and the receiving system. You should examine Event Viewer to find information about why the failures are occurring and troubleshoot from there.

SQL Script: Most Recent Duration for BizTalk Server SQL Server Agent Jobs

This query reports the time and duration of the last run of each BizTalk Server SQL Server Agent job. This gives you a good idea of the performance of your SQL Server resources. These are the jobs that clean and purge your message boxes and copy messages to the BizTalk Server Tracking database. If these are running slowly, there is a good chance that your system is running slowly.

Type the following SQL code into SQL Query Analyzer and then click the Execute button or press F5:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

CREATE TABLE #Temp (JobID uniqueidentifier,JobName sysname,Status int,DateStarted int,TimeStarted int,Duration int,OpEmailed nvarchar(20),OpNetSent nvarchar(20),OpPaged nvarchar(20),Retries int,Server nvarchar(30))

CREATE TABLE #Final (MessageBox sysname,JobName sysname,DateStarted int,TimeStarted int,Duration int,Status int)

declare @dbName sysname
declare MyCursor cursor FAST_FORWARD FOR
select DBName FROM [BizTalkMgmtDb]..[adm_MessageBox] WITH (NOLOCK)

open MyCursor

FETCH NEXT FROM MyCursor INTO @dbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_DeadProcesses_Cleanup_' + @dbName + '''')

INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_Message_Cleanup_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''MessageBox_Parts_Cleanup_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''PurgeSubscriptionsJob_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

INSERT INTO #Temp
exec ('[msdb]..[sp_help_jobhistory] @job_name = ''TrackedMessages_Copy_' + @dbName + '''')
INSERT INTO #Final
SELECT TOP 1 @dbName, JobName, DateStarted, TimeStarted, Duration, Status FROM #Temp
TRUNCATE TABLE #Temp    

FETCH NEXT FROM MyCursor INTO @dbName
END

SELECT * FROM #Final ORDER BY MessageBox

close MyCursor
deallocate MyCursor

drop table #Temp
drop table #Final

Table 9 How to use the results of the SQL Server Agent jobs script

Results Do this

If you notice that the duration of the SQL Server Agent jobs increasing, you could be running out of CPU bandwidth or your disk space might be getting low or fragmented.

First, examine the amount of free disk space on your SQL Server computers.

If you have sufficient free space, examine the Performance Monitor of your SQL Server computers.

Show:
© 2015 Microsoft