Tuesday, April 2, 2013

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.


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.
Keeps information from the latest backups of the archiving job of from the tracking database.
dta_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.
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_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.
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
Contains information of tracking data in the tracking database
Information for the spool table in the tracking database
Monitors all active instances in the tracking database
btsv_Tracking_FragmentsContains fragments of all messages in the tracking database
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:
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]
SELECT [uidInstanceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)
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)

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)

1 comment:

  1. • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online course Bangalore