My contributions to TechNet Gallery

As I like to research how BizTalk's interiors work, I often find myself writing queries against BizTalk's databases. Until now I've put literally a handfull of those queries at TechNet Gallery.

Here follows a description of those queries:

View Hosts and Host Instances Information from a SQL Query
This query shows information about the configured Hosts and the Host Instances within your BizTalk Group(s). The query becomes run against the BizTalkMgmtDb database.

It shows the following information:
• The name of the BizTalk Group
• The name of the Host
• Host Type (In-process or Isolated)
• 32-bits only (Yes or No)
• Tracking Host (Yes or No)
• The name of the Server where the Instance exists
• Name of the Host Instance
• Disabled from starting (Yes or No)

The result is sorted on:
• BizTalk Group
• Host Name
• Server Name

Show BizTalk Applications, Orchestrations, Ports and Pipelines with a SQL query
This query queries the BizTalk Management database. In one view it shows the following information:
• The BizTalk Applications from the BizTalk Group at hand
• The Assemblies which are deployed to the Applications
• The Orchestrations which are in the Applications
• The Status of the Orchestrations:

1. = Unenlisted 
2. = Stopped 
3. = Started

• The Ports as defined in the Orchestrations
• The Port Types
• The Port Type Operations
• The Receive Ports as they are bound to the Orchestrations
• The Receive Locations as they belong to the Receive Ports
• The Receive Pipelines as they are configured at the Receive Locations
• The Send Ports as they are bound to the Orchestrations
• The Send Pipelines as they are configured at the Send Ports
• The Send Port Groups as they are bound to the Orchestrations

The output is sorted on:
• Application
• Assembly
• Orchestration

Number of records in the MarkLog table
This query returns the number of records found in the MarkLog table of your BizTalk databases. This table is filled by the Backup BizTalk Server job, but is not cleaned up, unless you use the Terminator tool. Read this article to purge the records in the MarkLog table.

If you have more than 150.000 records in any of your MarkLog tables, you should use Terminator to purge these records.

The query can be easily extendedwith any custom databases which are also backed up by the Backup BizTalk Server job.

Show BizTalk Subscriptions
This download contains a .SQL query, which can be executed against your BizTalk Message Box. The results you get are the current subscriptions in the Message Box. Only the 'Equals Predicates' subscriptions are shown.
 
The following fields are returned:
• Application - Name of the BizTalk Application which contains the orchestration for this subscription
• Orchestration - Name of the orchestration which has the subscription
• Created On - The timestamp when the subscription was created
• Equals Predicate - The Equals Predicate (mostly the promoted property on a schema)
• Value - The value part from the predicate

Get Orchestration Instances, Counts and States
When this script is being run against a MessageBox it gives you the following information:

• Name of orchestration instances
• Number of orchestration instances
• State of the orchestration instances

The view is grouped by the first 2 columns and sorted on all 3 columns. To make it safe to execute this query against the MessageBoxDb, the WITH(NOLOCK) hint is used.

Number of Messages per MessageType in the TrackingDb
This query gives you insight in what information is stored in the BizTalk TrackingDb. When being run against it, the query shows the following columns:

• Schema
• Number of Messages

The result is grouped by and sorted on the field Schema. To make it safe to execute this query against the MessageBoxDb, the WITH(NOLOCK) hint is used. If you query the BizTalk databases yourself, never forget to add the WITH(NOLOCK) hint.


Hope these queries are helpfull for you. I'll certainly add more queries to TechNet Gallery.