Useful BizTalk SQL queries

Marklog:
Marklog is a table in the BizTalk databases, it store a string every time BizTalk backups the database, this is a known bug in BizTalk 2006 – 2009. And it will most probably never be fixed, make sure the marklog table isn’t too big, it might give you some performance issues.

SELECT [MarkName] FROM [BizTalkDTADb].[dbo].[MarkLog] WITH (NOLOCK)

See count of all messages and port name for a specified date. (as long as you have tracking on all receive and send ports.

SELECT CONVERT(char(10), [BizTalkDTADb].[dbo].[dta_MessageInOutEvents].[dtInsertionTimeStamp], 101) AS InDate, COUNT([BizTalkDTADb].[dbo].[dta_MessageInOutEvents].[nPortId]) AS numb, dta_PortName.strPortName AS name FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK) INNER JOIN [BizTalkDTADb].[dbo].[dta_PortName] WITH (NOLOCK) ON [BizTalkDTADb].[dbo].[dta_MessageInOutEvents].[nPortId] = [BizTalkDTADb].[dbo].[dta_PortName].[nPortId] WHERE [BizTalkDTADb].[dbo].[dta_MessageInOutEvents].[dtInsertionTimeStamp] BETWEEN CONVERT(datetime, ‘05-10-2011 00:00:00′, 120) AND CONVERT(datetime,‘05-17-2011 23:59:59′, 120) GROUP BY CONVERT(char(10), [dta_MessageInOutEvents].[dtInsertionTimeStamp], 101), [dta_PortName].[strPortName] ORDER BY InDate, numb DESC

The last is just a simple count of all messages registered in the MessageInOutEvents.

SELECT Convert(char(10), [dtInsertionTimeStamp], 101) as date, count(Convert(char(10), [dtInsertionTimeStamp], 101)) as ant FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK) WHERE [dtInsertionTimeStamp] BETWEEN convert(datetime, ‘05-10-2011 00:00:00′, 120) AND convert(datetime, ‘05-17-2011 23:59:59′, 120) GROUP BY Convert(char(10), [dtInsertionTimeStamp], 101) ORDER BY [date]

Feel free to use these queries as you wish. (remember to change the date in the query).

The following query will tell you when during a day you have the most queries passing through BizTalk.


SELECT datepart(HH, [dtInsertionTimeStamp]) as date,
count(Convert(char(10), [dtInsertionTimeStamp], 108)) as ant FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK) WHERE [dtInsertionTimeStamp] BETWEEN convert(datetime, ‘01-01-2011 00:00:00′, 120) AND convert(datetime, ‘07-08-2011 23:59:59′, 120) GROUP BY datepart(hh, [dtInsertionTimeStamp])ORDER BY [date]

all you need to do is change the date after the WHERE clause.

Make sure the dates are within the time you have tracking stored, and the query execution time will vary depending on how many massages you have during a day and for how long you want to gather the information.