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

Tables

This section will cover all tables, the one bolded out has information of them. This will be updated as soon as possible.

BizTalkDBVersion
- This table stores the information of the current version of your BizTalk environment. Since each BizTalk database has this table, it can also be used for identification of the database at hand, see Identifying BizTalk Databases
dta_Adapter
- This table stores all the adapters installed in your environment, FTP, FILE etc. including all third-party adapters.
dta_ArchiveHistory
- Keeps information from the latest backups of the archiving job of from the tracking database.
dta_CallChain
- This table contains shape execution and nested orchestration call information.
dta_CallChainTemp
- Temporary table for orchestration debugger Call Chain table.
dta_Cubes
dta_DebugTrace
- This table stores information from orchestration, this data is used for the Orchestration debugger. If you have set 'Shape start and end' tracking on your orchestrations, data will be written for these events in this table.
dta_DebugTraceTemp
- Temporary table for orchestration debugger tracking table.
dta_DecryptionSubject
dta_Group
- Contains information of the BizTalk group.
dta_Host
- Stores information of all the hosts in the environment
dta_Items
- This table stores information of the used tables in the tracking database, including friendly name and type id.
dta_ItemTypes
- Stores information of the type name and type id for the Items table.
dta_MessageBox
- Stores information of the messageboxes and related info regarding it, or them.
dta_MessageFields
- Contains information about tracked Promoted Properties
dta_MessageFieldValues
- Contains information about tracked Promoted Properties
dta_MessageFieldValuesTemp
- Will come back to this table
dta_MessageInOutEvents
- Stores information of all in and out events of your BizTalk database
dta_MessageInOutEventsTemp
- Temporary information for in and out events.
dta_MessageStatus
- This tables is predefined and includes the state ID for the different message states.
dta_PartyName
- Contains id and name for parties.
dta_PortName
- Contains all information of all ports, send and receive ports.
dta_ProcessState
- Name of the different states.
dta_Rules
- Stores information of all the rules in the Business rules engine
dta_RulesAgendaUpdates
- Keeps information of updates in a rule that has tracking turned on.
dta_RulesConditionEvaluation
dta_RuleSetEngineAssociation
dta_RuleSets
dta_RulesFactActivity
dta_RulesFired
dta_SchemaName
- Contains Id and Schemaname of processed messages
dta_ServiceInstanceExceptions
- This table stores error information for any suspended service instance.
dta_ServiceInstances
- Stores information of all service instances.
dta_ServiceInstancesTemp
- Temporary table for service instances.
dta_Services
- Holds information of all services that has passed in BizTalk.
dta_ServiceState
- Defines the different states with state name of the services, in the above table.
dta_ServiceSymbols
- Contains some xml for each service which is deployed, see Hidden Gem in BizTalk 2006 R2
dta_SigningSubject
EdiMessageContent
- Contains message information which is written by AS2 Decoder pipeline component, see Hooking into BizTalk’s underlying transaction in your custom receive pipeline component
EdiMessagePartContent
- Contains message information which is written by AS2 Decoder pipeline component, see Hooking into BizTalk’s underlying transaction in your custom receive pipeline component
MarkLog
- This table holds all the transaction marks set to this database during backup. Each (BizTalk) database which is being backed up by the 'Backup BizTalk Server' job has this table. Note that there is no job that cleans this table! You need to run the terminator tool to clean it up. See also: Clean up the MarkLog table with Terminator
TDDS_FailedTrackingData
- Tracked messages that failed on transfer from the Message box to the tracking database.
TDDS_StreamStatus
- Status of the message stream from the TDDS
TrackingData
- Contains information of tracking data in the tracking database
TrackingDataPartitions
- TrackingMessageReferences
TrackingSpoolInfo
- Information for the spool table in the tracking database
RunningInstances
- Monitors all active instances in the tracking database
btsv_Tracking_Fragments
- Contains fragments of all messages in the tracking database
Tracking_Parts
- Contains information of the parts of a message in the tracking database
Tracking_Spool
- The spool table for the Tracking data

Tables to be aware of

There are a few tables to monitor and make sure are not crossing its secret border. This may differ from company to company. But the databases that gets the most load are:

dta_ServiceInstances
- Contains information of all instances, this is turned on by default if you have default tracking on, therefore this table may get very big if jobs aren't running as they should.
dta_MessageInOutEvents
- This table contains data of all in and out events of BizTalk, Receive and send ports. This one is also on by default and can only be turned off by turning global tracking off.
dta_DebugTrace
- This table contains information for the Orchestration Debugger, in case you have this turned on in all application, or have orchestrations that are doing a lot of work this table may get very big as well.

SQL Queries

Be aware that all queries towards the BizTalk databases should be with a NO LOCK

Find Orphaned messages in the tracking database

SELECT count(*) from [BizTalkDTAdb].[dbo].[dta_ServiceInstances]

WHERE dtEndTime is NULL and


[uidServiceInstanceId]

NOT IN (

SELECT [uidInstanceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)

UNION

SELECT [StreamID]

FROM [BizTalkMsgBoxDb].[dbo].[TrackingData] WITH(NOLOCK))

Query Transactions

NOTE: Change red text into date (DD-MM-YYYY HH:MM:SS)

SELECT datepart(hh, [dtInsertionTimeStamp]) as timeMsg, datepart(dd, [dtInsertionTimeStamp]) as


dateMsg,

count(Convert(char

(10),
 [dtInsertionTimeStamp], 108))

as ant

FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH


(NOLOCK)

WHERE [dtInsertionTimeStamp]

BETWEEN convert(datetime, '11-03-2011 23:00:00'

, 120)

AND convert

(datetime,

'11-04-2011 22:59:59', 120)

GROUP BY


datepart(hh, [dtInsertionTimeStamp]), datepart(dd, [dtInsertionTimeStamp])

ORDER

BY

[dateMsg ]ASC, [timeMsg]

Query Instances

NOTE: Change red text into date (DD-MM-YYYY HH:MM:SS)

SELECT datepart(hh, [dtInsertionTimeStamp]) as timeMsg, datepart(dd, [dtInsertionTimeStamp]) as


dateMsg,

count(Convert(char

(10),
 [dtInsertionTimeStamp], 108))

as ant

FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] WITH (NOLOCK)

WHERE [dtInsertionTimeStamp]

BETWEEN convert(datetime ,'11-03-2011 23:00:00' 120) AND


convert

(datetime, '11-04-2011 22:59:59*, 120)

GROUP BY


datepart(hh, [dtInsertionTimeStamp]), datepart(dd, [dtInsertionTimeStamp] )

ORDER

BY

[dateMsg] ASC, [timeMsg]

Query transaction by host name

NOTE: Change red text into date (DD-MM-YYYY HH:MM:SS)

SELECT dbo.dta_Host.strHostName, count

(dbo.dta_ServiceInstances.dtInsertionTimeStamp)

as

ant

FROM dbo.dta_Host WITH (NOLOCK)

INNER JOIN

dbo.dta_ServiceInstances

WITH


(NOLOCK)

ON


dbo.dta_Host.nHostId= dbo.dta_ServiceInstances.nHostId

WHERE dbo.dta_ServiceInstances.[dtInsertionTimeStamp]

BETWEEN convert(datetime, '10-30-2011 23:00:00'

, 120)

AND convert

(datetime,

'10-31-2011 22:59:59', 120)

GROUP BY


strHostName

ORDER

BY

ant DESC

Get message count for all applications from In Out Events

use biztalkdtadb

SELECT COUNT

(dbo.dta_MessageInOutEvents.dtTimestamp)

AS


ant, BizTalkMgmtDb.dbo.bts_application.nvcName

FROM dbo.dta_MessageInOutEvents WITH (NOLOCK)

INNER JOIN

BizTalkMgmtDb.dbo.bts_receiveport

WITH


(NOLOCK)

INNER JOIN

dbo.dta_PortName

WITH


(NOLOCK)

ON


BizTalkMgmtDb.dbo.bts_receiveport.nvcName = dbo.dta_PortName.strPortName

ON

dbo.dta_MessageInOutEvents.nPortId = dbo.dta_PortName.nPortId

INNER JOIN

BizTalkMgmtDb.dbo.bts_application

WITH


(NOLOCK)

ON


BizTalkMgmtDb.dbo.bts_receiveport.nApplicationID = BizTalkMgmtDb.dbo.bts_application.nID

GROUP BY


BizTalkMgmtDb.dbo.bts_application.nvcName

order by

ant

desc

NOTE: This will only look for receive ports (which is more than enough)

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

BizTalk Server 2010: Database BizTalkDTADb

BizTalk Server 2010: Database BizTalkDTADb

Table of Contents

Tables

Tables to be aware of

SQL Queries

Find Orphaned messages in the tracking database

Query Transactions

Query Instances

Query transaction by host name

Get message count for all applications from In Out Events

References

See Also