Share via

BizTalk Server 2010: MessageBox Database Tables

  • Article

Introduction

This article will contain information about the BizTalkMsgBoxDb database. Including references to articles that is important for this database. If you have some more information regarding tables, please update them accordingly.

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.

Tables

This blog 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 holding the messageID for a message

5

ApplicationProps

All the host properties are listed in this table

6

Applications

Applications tables holds the host details of the BizTalk Group

7

BitwiseANDPredicates

Predicates for Orchestration 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 ith 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 ith 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.
 If BizTalk receives a message that is larger than the Large Message Size threshold, the message is split into fragments and each fragment is then saved in this 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 store 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. The first four TrackingData_0_ tables are a staging area for BAM activity data. This data is eventually moved to the BAMPrimaryImport database tables by the TDDS subservice.
The second four TrackingData_1_ tables are a staging area for DTADb tracking data. This data is eventually moved to the DTADb database tables by the TDDS subservice.

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

 

References

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.