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:
- = Unenlisted
- = Stopped
- = 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.