Hi,

I would like to know the number of messages BizTalk (2006R2) processed on a given day. please help me to achieve this. Backup jobs store only 2 days data and all purging,cleanup jobs are running fine.

• Edited by sugata kar Tuesday, January 27, 2015 6:04 AM

Hi,

Download this sql script. BizTalk: Message count per Application

How it works: http://blogs.msdn.com/b/amantaras/archive/2014/10/09/number-of-messages-per-application-dtadb-data.aspx

Another script provided by Tord to get the Statistics from in and out events in BizTalk

This Script will provdie you with information of transactions in your BizTalk Environment.

The scripts checks the InOutEvents table in the BizTalkDTADb database. It will group by date (DD) and Hour(HH)

For more useful BizTalk SQL queries

Hi Rachit..thanks for your reply.

But i think these queries is only applicable if tracking is enabled. In production i don't have any tracking set in any artifacts.

Sujata,

In that case you can read the instance table in the BizTalkMsgBoxDb database and cross join it with the host instance in the BizTalkMgmtDb which will give you the amount of messages being processed by host instances.

This script WILL NOT give you information of all instances in your BizTalk environment. ONLY instences being processed by a server and host instance.

Here the script provided by Tord Instances in Messagebox being processed by a host instance

You can also make use of existing BizTalk performance counters to monitor the messages.

PerformanceCounter msgsReceivedCounter = new 
    PerformanceCounter("BizTalk:Messaging", "Documents received", "ReceiveHost", "BizTalkServer");
msgsReceivedCounter.ReadOnly = true;
PerformanceCounter msgsSentCounter = new 
    PerformanceCounter("BizTalk:Messaging", "Documents processed", "SendHost", "BizTalkServer");
msgsSentCounter.ReadOnly = true;

lblSent.Text = string.Format("{0}",  msgsSentCounter.NextValue());
lblReceived.Text = string.Format("{0}",  msgsReceivedCounter.NextValue());

But this has its own limitations. Refer: Get Biztalk's message count through C# .NET

To get how many messages BizTalk sends/receives per day (MsgBoxDB),  look for Perfmon counters such as \BizTalk Messaging\Documents received or processed etc: http://msdn.microsoft.com/en-us/library/aa558731(BTS.20).aspx.

You can be more specific and find out how many messages the FILE adapter picked up for example: http://msdn.microsoft.com/en-us/library/aa559528(BTS.20).aspx.

Alternatively, If use HAT, option Queries -> Message counts, you can see the query that HAT use.
You can modify to your requirements.

If you want to know how many messages run through the BizTalk Server, I think you can reach to that number by (Message Received + Messages Send) option (+ Messages Transmission failures)

Rachit

Hi Rachit..thanks for your reply.

But i think these queries is only applicable if tracking is enabled. In production i don't have any tracking set in any artifacts.

When youre using BizTalk 2006 R2, have you considered the tool Health and Activity Tracking (HAT) ?

There are some useful queries like the one youre looking for. You get the query from the text-area by click the button Show Query and can still run it SQL Management studio, which I used to more often. Or you can modify those queries to fit your need.

For example:

Under Queries options Message Counts, will give the count of messages for today, last 2,7,14 days..

Or the following query will give you the count of message for yesterday which modified after getting the query from this excellent tool.

DECLARE @Timestamp as DATETIME
SET @Timestamp= GETUTCDATE() - 1  -- local time converted to UTC

DECLARE @MessageFailures as INT
DECLARE @MessagesSent as INT
DECLARE @MessagesReceived as INT

-- Count Failures--
SELECT @MessageFailures=COUNT(*) 
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE 
     st.nMessageStatusId = 5 
    AND [Event/Timestamp] > @Timestamp

-- Count Sends --
SELECT @MessagesSent=COUNT(*) 
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE 
     st.nMessageStatusId = 1 
    AND [Event/Timestamp] > @Timestamp

-- Count Receives--
SELECT @MessagesReceived=COUNT(*) 
FROM [dbo].[dtav_MessageFacts] mf WITH (READPAST)
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE 
     st.nMessageStatusId = 0 
    AND [Event/Timestamp] > @Timestamp

-- Print Results --
SELECT 
     @MessagesReceived as "Messages Received", 
     @MessagesSent as "Messages Sent",
     @MessageFailures as "Message Transmission Failures"

Hi Ashwin,

I executed the query in SQL but didn't get any count. I don't have any record in dtav_MessageFacts table.

Hi Sugata,

Then you don't have any option to know the count of messages processed. You just have few options:

• You must have data in tracking db see the count as this is tracking information.
• Or you must have BAM configured (which is again another way tracking the KPIs)

When you want to know the number of messages BizTalk processed on a given day, they must have been tracked otherwise, sorry you dont have option.

Hi Ashwin,

Thanks for the information. So, other than setting BAM/Tracking on, how i can capture the data?One thing i can consider is to write custom send/receive pipelines; but is there any other ways to achieve this?

Hi,

Without tracking you cant get this data.

You dont need to enable tracking on the port for this to return you the result set. With port level tracking you can save the message and its context. But without port level tracking you can still get the count of message processed which youre after. Give it a try.

Sugata,

We can suggest many different ways to get the count of message processed in a day like the following.:

• Use a archive pipeline in Receive location and send port which simply archive the message received and send. And have a code or manually get the count of message at a specific date time.
• Use a custom pipeline component, which will simply update a counter in any data store.
• Blah..blahh..etc

But they not the correct solutions. When a feature is provided in BizTalk for this requirement, I would use it and trying to find something else which may provide the solution but not in a correct way. Option is yours.

Sujata,

In that case you can read the instance table in the BizTalkMsgBoxDb database and cross join it with the host instance in the BizTalkMgmtDb which will give you the amount of messages being processed by host instances.

This script WILL NOT give you information of all instances in your BizTalk environment. ONLY instences being processed by a server and host instance.

Here the script provided by Tord Instances in Messagebox being processed by a host instance

You can also make use of existing BizTalk performance counters to monitor the messages.

PerformanceCounter msgsReceivedCounter = new 
    PerformanceCounter("BizTalk:Messaging", "Documents received", "ReceiveHost", "BizTalkServer");
msgsReceivedCounter.ReadOnly = true;
PerformanceCounter msgsSentCounter = new 
    PerformanceCounter("BizTalk:Messaging", "Documents processed", "SendHost", "BizTalkServer");
msgsSentCounter.ReadOnly = true;

lblSent.Text = string.Format("{0}",  msgsSentCounter.NextValue());
lblReceived.Text = string.Format("{0}",  msgsReceivedCounter.NextValue());

But this has its own limitations. Refer: Get Biztalk's message count through C# .NET

To get how many messages BizTalk sends/receives per day (MsgBoxDB),  look for Perfmon counters such as \BizTalk Messaging\Documents received or processed etc: http://msdn.microsoft.com/en-us/library/aa558731(BTS.20).aspx.

You can be more specific and find out how many messages the FILE adapter picked up for example: http://msdn.microsoft.com/en-us/library/aa559528(BTS.20).asp

 :)