BizTalk Server 2010: Database BizTalkDTADb

BizTalk Server 2010: Database BizTalkDTADb

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
  • 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 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)

References

See Also

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

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Maheshkumar S Tiwari edited Revision 42. Comment: typo error

  • Steef-Jan Wiggers edited Revision 41. Comment: Formatting, adjusted outline

  • Lex Hegt edited Revision 37. Comment: Added information about dta_MessageFields and dta_MessageFieldValues

  • Lex Hegt edited Revision 36. Comment: Added information regarding the EdiMessageContent and EdiMessagePartContent

  • Lex Hegt edited Revision 35. Comment: Added information regarding dta_ServiceSymbols table

  • Lex Hegt edited Revision 34. Comment: Added info about the dta_ServiceInstanceExceptions table

  • Lex Hegt edited Revision 33. Comment: Added info about dta_CallChain and dta_CallChainTemp tables

  • Lex Hegt edited Revision 31. Comment: Added link

  • Steef-Jan Wiggers edited Revision 27. Comment: Fixed typos

  • Tord G.Nordahl edited Revision 21. Comment: added one more query

Page 1 of 3 (28 items) 123
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Tord G.Nordahl edited Original. Comment: added some more information

  • Tord G.Nordahl edited Revision 1. Comment: added more content

  • Tord G.Nordahl edited Revision 2. Comment: Added all the tables, more information regarding them will be added too

  • Tord G.Nordahl edited Revision 3. Comment: removed the first heading

  • Tord G.Nordahl edited Revision 4. Comment: fixed the database tables

  • Tord G.Nordahl edited Revision 5. Comment: added some more info, more will come. :)

  • Tord G.Nordahl edited Revision 6. Comment: added even more information

  • Tord G.Nordahl edited Revision 7. Comment: added refences

  • Tord G.Nordahl edited Revision 8. Comment: added some sql queries

  • Tord G.Nordahl edited Revision 9. Comment: added more text

  • Tord G.Nordahl edited Revision 10. Comment: code cleanup

  • Tord G.Nordahl edited Revision 11. Comment: formatting

  • Tord G.Nordahl edited Revision 12. Comment: formatting