Share via

BizTalk Server 2010: Database BizTalkDTADb

  • Article

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 about 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
  • dta_SigningSubject
  • EdiMessageContent
  • EdiMessagePartContent
  • 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 get the most load are:

  • dta_ServiceInstances
    • Contains information of all instances, this is turned on by default if you have a 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)

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.