Click here to Skip to main content
Click here to Skip to main content

MessageBox Database Tables

, 26 Feb 2014
Rate this:
Please Sign up or sign in to vote.
Quick view of Biztalk MessageBox Tables

MessageBoxDB

MessageBox database is known as the heart of BizTalk Engine. Publish-Subscribe Architecture completely relies on this database.

The logical routing of messages, fulfilling subscriptions and message tracking are carried out through this database.

This tip contains the list of Messagebox DB tables and their details.

S.No Table Name Details
1 ActiveRefCountLog If a message is being subscribed by multiple subscribers, There will be MessageRefCountLogs which will aggregate the list of subscribers per message, once this count reaches the maximum value, SQL server agent job will carry out this aggregation and deletes those messages from DB
2 adap_DownloadedFiles FTP Adapter: FTP URI and the timestamp comparisonrecorded is configuration to this table. This will be useful for the property EnableTimeStampComparison
3 adap_UriKeys FTP Adapter: FileName and FileTimeStamp is being recorded in this table to track the details of the file being downloaded from the FTP location
4 AddRef This table holds the messageID for a message
5 ApplicationProps Application name with property ID and its value is being recorded in this table
6 Applications Applications tables holds the host details of the BizTalk Group
7 BitwiseANDPredicates Predicates for Orchestarion Subscriptions are listed in this table
8 BizTalkDBVersion This table holds the information about the Biztalk Message Box DB
9 BizTalkServerApplication
_DequeueBatches
The Dequeued messages are recorded in this table per host
10 BizTalkServerApplication
_MessageRefCountLog
Subscription based ref count log will be recorded based on the host instance table
11 BizTalkServerApplicationQ A Queue, which holds the number of messages to be processed/waiting to be delivered is being listed in this table
12 BizTalkServerApplicationQ
_Scheduled
A Scheduled Queue table, which holds the processed messages and waiting for the transmit based on service window interval specified on the port
13 BizTalkServerApplicationQ
_Suspended
References to suspended messages in BizTalkServerApplication host will be recorded in this table. The messages resides till it has got terminated or resumed
14 BizTalkServerIsolatedHost
_DequeueBatches
The Dequeued messages are recorded in this table per host
15 BizTalkServerIsolatedHost
_MessageRefCountLog
Subscription based ref count log will be recorded based on the host instance table
16 BizTalkServerIsolatedHostQ A Queue, which holds the number of messages to be processed/waiting to be delivered is being listed in this table
17 BizTalkServerIsolatedHostQ
_Scheduled
A Scheduled Queue table, which holds the processed messages and waiting for the transmit based on service window interval specified on the port
18 BizTalkServerIsolatedHostQ
_Suspended
References to suspended messages in BizTalkServerIsolatedHost host will be recorded in this table. The messages resides till it has got terminated or resumed
19 btsmon_Instances BizTalk Monitoring tables which will hold the references of Subscriptions(Active/Instance)
20 btsmon_InstanceSubscriptions
21 btsmon_RunningInstances
22 btsv_ActivationSubscription The Activation subscription based details per host instance is listed in this table
23 btsv_LocalDate Biztalk System Server date is loaded in this table by default
24 btsv_Tracking_Fragments All Tracking related details are listed in this table
25 btsv_Tracking_Parts
26 btsv_Tracking_Spool
27 btsv_UTCDate UTC Date format is loaded in this table by default
28 ConvoySetInstances Active Correlation of the message instances are recorded in this table
29 ConvoySets All the convoy sets will be listed in this table
30 DynamicStateInfo
_BizTalkServerApplication
This table contains all the dynamic Orchestration information, that will be loaded during runtime, it has a row for each deployed orchestration per host
31 DynamicStateInfo
_BizTalkServerIsolatedHost
32 EdiControlNumbers This table is used to find the ISA control number for a trading partner of an EDI message
33 EdiIncomingEdifactICN Holds the value of OnewayAggrement id, ICA13 and Received time
34 EdiIncomingX12ICN Holds the value of OnewayAggrement id, UNB05 and Received time
35 EdiInt_InboundMessageIdentity This table holds the value of AS2From, AS2To, MessageID and Time Inserted for an EDI message
36 EdiInt_Mic If an MDN Ack is enabled for an EDI message, when the AS2 send pipeline processes an outbound message, it computes a MICHashValue. The send pipeline saves the hash value in the EdiInt_Mic table of the BizTalkMsgBoxDb database.
37 EqualsPredicates The operator(==) used in the expression of the subscription information is placed in the Predicate tables.
38 EqualsPredicates2ndPass The operator(==) used in the expression of the subscription information is placed in the Predicate tables.
39 ExistsPredicates The operator(exists) used in the expression of the subscription information is placed in the Predicate tables.
40 FirstPassPredicates The operator(First pass) used in the expression of the subscription information is placed in the Predicate tables.
41 Fragments Message Fragments table
42 GreaterThanOrEqualsPredicates The operator(>=) used in the expression of the subscription information is placed in the Predicate tables.
43 GreaterThanPredicates The operator(>) used in the expression of the subscription information is placed in the Predicate tables.
44 Instances All the message instance related details are recorded in these tables
45 InstancesOperatedOn
46 InstancesPendingOperations
47 InstancesSuspended
48 InstanceStateMessageReferences
_BizTalkServerApplication
This Queue holds the messages which has been processed and persisted in database which will be needed in later stage in Orchestration or MSMQ.
49 InstanceStateMessageReferences
_BizTalkServerIsolatedHost
50 JobData List of SQL jobs are listed in this table
  • MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
  • TrackedMessages_Copy_BizTalkMsgBoxDb
  • PurgeSubscriptionsJob_BizTalkMsgBoxDb
51 LessThanOrEqualsPredicates The operator(<=) used in the expression of the subscription information is placed in the Predicate tables.
52 LessThanPredicates The operator(<) used in the expression of the subscription information is placed in the Predicate tables.
53 LocalizedErrorStrings All the Errors messages are listed in this table
54 MarkLog Marklog is a table that stores a string every time BizTalk backups the tracking database
55 MessageParts The Biztalk messages are split into message parts. The UIDPartID is recorded in this table, which is used to retrieve the actual message in the parts table
56 MessagePredicates All message related predicates are listed in this table
57 MessageProps This table holds the contextual property of a message and also records the batch of message and the order in which the messages arrived
58 MessageRefCountLog1 For a multiple subscriber based message, the count of subscribers are updated in this table. Two tables are used to reduce contention and locking issues
59 MessageRefCountLog2
60 MessageRefCountLogTotals
61 MessageZeroSum When a message has been subscribed by all of its subscribers, reference of a message will be inserted into this table and the clean up job will permanently delete this message from message box
62 Modules All the application in the BAC are listed as modules in this table
63 NotEqualsPredicates The operator(!=) used in the expression of the subscription information is placed in the Predicate tables.
64 OperationsProgress This table tracks the SPID, succeeded count, failed count, pending count and skipped count
65 PartRefCountLog1 For a multiple subscriber based message, the count of subscribers for a message parts are updated in this table. Two tables are used to reduce contention and locking issues
66 PartRefCountLog2
67 PartRefCountLogTotals
68 Parts This table holds the actual message body content which will be in binary encoded format in the column imgpart
69 PartZeroSum When a message has been subscribed by all of its subscribers, reference of a message will be inserted into this table and the clean up job will permanently delete this message from message box
70 PredicateGroup Multiple Predicate for a message will be grouped with 'and', 'or' condition in this table
71 PredicateGroupNames Group of Predicates with subscription name are listed in this table
72 PredicateGroupZeroSum1 After the messages are processed, the subscriptions has to be deleted from Messagebox DB by inserting the predicate group into this table
73 PredicateGroupZeroSum2
74 ProcessHeartbeats Heartbeat messages are communicated every one minute to handle the resources in a right way in biztalk to have coordinate process like transfer data from message box to tracking DB, etc. The Configuration for heart beat messages per host is configured in this table
75 Release MessageID is recorded in this table
76 ServiceClasses The following services are responsible for creating subscriptions in BizTalk Server
  • Messaging Isolated Host
  • Orchestration
  • Messaging InProcess
  • MSMQt
  • Cache

    are listed in this table

77 Services All service related info are present in this table
78 Spool This table contains the description of the messages and message context properties of a message whichever passes through biztalk
79 StaticStateInfo This table contains all the static Orchestration information, that will be loaded during runtime, it has a row for each deployed orchestration
80 Subscription All the Message subscription related details are recorded in this table
81 Tracking_Fragments1 All the tracked messages will be listed in this table whenever Health and Activity Tracking (HAT) to track message bodies for pipelines and orchestrations
And Tracking Type property for a send port or a receive port to Before Receive or After Receive are enabled., these table will be recording the desired details.
For performance consideration, these tables are often purged.
82 Tracking_Fragments2
83 Tracking_Parts1
84 Tracking_Parts2
85 Tracking_Spool1
86 Tracking_Spool2
87 TrackingData
88 TrackingData_0_0
89 TrackingData_0_1
90 TrackingData_0_2
91 TrackingData_0_3
92 TrackingData_1_0
93 TrackingData_1_1
94 TrackingData_1_2
95 TrackingData_1_3
96 TrackingDataPartitions
97 TrackingMessageReferences
98 TrackingSpoolInfo
99 TruncateRefCountLog Truncate type and truncate count are recorded in this table
100 TrustedUsers Host Instances with the Specified Trusted UserName will be listed in this table
101 UniqueSubscription Unique subscription details are recorded in this table

Hope this helps...!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 26 Feb 2014
Article Copyright 2014 by VIGNESH SUKUMAR
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid