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

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.

BizTalkDBVersionThis tables store 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 BizTalkAdminBlogging  
dta_AdapterThis 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
dta_CallChainTemp
dta_Cubesdta_DebugTraceThis 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_DebugTraceTempTemporary table for orchestration debugger tracking table.
dta_DecryptionSubject
dta_Group
Contains information of the BizTalk group.
dta_HostStores information of all the hosts in the environment
dta_ItemsThis table stores information of the used tables in the tracking database, including friendly name and type id.
dta_ItemTypesStores information of the type name and type id for the Items table.
dta_MessageBoxStores information of the messageboxes and related info regarding it, or them.
dta_MessageFieldsWill come back to this table
dta_MessageFieldValuesWill come back to this table
dta_MessageFieldValuesTempWill come back to this table
dta_MessageInOutEventsStores information of all in and out events of your BizTalk database
dta_MessageInOutEventsTempTemporary information for in and out events.
dta_MessageStatusThis tables is predefined and includes the state ID for the different message states.
dta_PartyNameContains id and name for parties.
dta_PortNameContains all information of all ports, send and receive ports.
dta_ProcessStateName of the different states.
dta_RulesStores information of all the rules in the Business rules engine
dta_RulesAgendaUpdatesKeeps information of updates in a rule that has tracking turned on.
dta_RulesConditionEvaluation
dta_RuleSetEngineAssociation
dta_RuleSets
dta_RulesFactActivity
dta_RulesFired
dta_SchemaName
dta_ServiceInstanceExceptions
dta_ServiceInstancesStores information of all service instances.
dta_ServiceInstancesTempTemporary table for service instances.
dta_ServicesHolds information of all services that has passed in BizTalk.
dta_ServiceStateDefines the different states with state name of the services, in the above table.
dta_ServiceSymbols
dta_SigningSubject
EdiMessageContent
EdiMessagePartContent
MarkLogThis 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 this table does not clean itself, you need to run the terminator tool to clean it up.
TDDS_FailedTrackingDataTracked messages that failed on transfer from the Message box to the tracking database.,
TDDS_StreamStatusStatus of the message stream from the TDDS
TrackingData
Contains information of tracking data in the tracking database
TrackingDataPartitionsTrackingMessageReferences
TrackingSpoolInfo
Information for the spool table in the tracking database
RunningInstances
Monitors all active instances in the tracking database
btsv_Tracking_FragmentsContains fragments of all messages in the tracking database
Tracking_Parts
Contains information of the parts of a message in the tracking database
Tracking_SpoolThe 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, therefor this table may get very big if jobs aren't running as they should.
dta_MessageInOutEventsThis 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_DebugTraceThis 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)

http://social.technet.microsoft.com/wiki/contents/articles/7253.biztalk-server-2010-database-biztalkdtadb.aspx