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.
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:
Be aware that all queries towards the BizTalk databases should be with a NO LOCK
SELECT
count
(*)
from
[BizTalkDTAdb].[dbo].[dta_ServiceInstances]
WHERE
dtEndTime
is
NULL
and
[uidServiceInstanceId]
NOT
IN
(
[uidInstanceID]
FROM
[BizTalkMsgBoxDb].[dbo].[Instances]
WITH
(NOLOCK)
UNION
[StreamID]
[BizTalkMsgBoxDb].[dbo].[TrackingData]
(NOLOCK))
NOTE: Change red text into date (DD-MM-YYYY HH:MM:SS)
datepart(hh, [dtInsertionTimeStamp])
as
timeMsg, datepart(dd, [dtInsertionTimeStamp])
dateMsg,
Convert
char
(10), [dtInsertionTimeStamp], 108))
ant
[BizTalkDTADb].[dbo].[dta_MessageInOutEvents]
[dtInsertionTimeStamp]
BETWEEN
convert
(datetime,
'11-03-2011 23:00:00'
, 120)
AND
'11-04-2011 22:59:59'
GROUP
BY
datepart(hh, [dtInsertionTimeStamp]), datepart(dd, [dtInsertionTimeStamp])
ORDER
[dateMsg ]
ASC
, [timeMsg]
[BizTalkDTADb].[dbo].[dta_ServiceInstances]
(datetime ,
120)
(datetime, '11-04-2011 22:59:59*, 120)
datepart(hh, [dtInsertionTimeStamp]), datepart(dd, [dtInsertionTimeStamp] )
[dateMsg]
dbo.dta_Host.strHostName,
(dbo.dta_ServiceInstances.dtInsertionTimeStamp)
dbo.dta_Host
INNER
JOIN
dbo.dta_ServiceInstances
ON
dbo.dta_Host.nHostId= dbo.dta_ServiceInstances.nHostId
dbo.dta_ServiceInstances.[dtInsertionTimeStamp]
'10-30-2011 23:00:00'
'10-31-2011 22:59:59'
strHostName
DESC
use biztalkdtadb
COUNT
(dbo.dta_MessageInOutEvents.dtTimestamp)
AS
ant, BizTalkMgmtDb.dbo.bts_application.nvcName
dbo.dta_MessageInOutEvents
BizTalkMgmtDb.dbo.bts_receiveport
dbo.dta_PortName
BizTalkMgmtDb.dbo.bts_receiveport.nvcName = dbo.dta_PortName.strPortName
dbo.dta_MessageInOutEvents.nPortId = dbo.dta_PortName.nPortId
BizTalkMgmtDb.dbo.bts_application
BizTalkMgmtDb.dbo.bts_receiveport.nApplicationID = BizTalkMgmtDb.dbo.bts_application.nID
BizTalkMgmtDb.dbo.bts_application.nvcName
order
by
desc
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.
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
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