Hello,

I am having an issue with High Database Size throttling in a BizTalk 2010 environment.  I have ruled out most normal causes:

• Spool - it's high (250k), but not near 500k threshold
• Tracking - very low, near 0 most of the time

That leaves Database Size.  This is happening for 1 specific host; a dedicated receive host.  I have checked all the host queue tables, but they do not contain any records.

The Database Size performance counter shows this host having a database size of over 50k records quite often.  At this point Throttling starts and we restart the host which clears it temporarily.  It will start back up again.  This process is usually repeated 3-4 times a day.  I know this is not healthy for the system, but is the only way we've found to get documents flowing again.

My question is:  If the Host queue tables do not contain the 50k+ records to trigger throttling, where is BizTalk finding these records?  What query is BizTalk using to calculate Database Size?

My hope is that if I can find where the records are located, it will lead me to the process/orchestration/etc. I can fix to remove the High Database Size throttling. 

Other Background info that might be relevant:  We have Batching setup for Outbound EDI.  There are about 140 parties with batching setup.  These 140 batching orchestrations are always active.  They also appear to be holding on to old message references, which is why our Spool table is at 250k records, and constantly growing.  We can clear the references by restarting the batching orchestrations, but that is very time consuming, even using scripts.  the reason this might be relevant is that the receive host in question also receives outbound documents that get translated to the EDI that gets batched.

Thanks,

Rob

Database throttling can occur due to multiple reasons like if Spool Count is high, Tracking DB size is high, high records in the host tables(like suspended and other tables) etc.

I would suggest you to run Message Box Viewer tool, it will generate a report where you can look into the various parameters like- what is the DB or table size etc. 

Focus on the warnings it shows in RED.

Later you can use BizTalk Terminator tool if required.

Prashant,

Both MBV and Terminator Tool were run last night, but neither helped with this issue.  MBV does show some issues, but nothing related to database size.  TT run cleared out MarkLog and repaired RefCounts, but that's it.  Throttling is still occuring.

We have several other applications running successfully on the same servers, but they are on different hosts and do not have throttling issues.

We do have one SQL job that has problems: "Monitor BizTalk Server (BizTalkMgmtDb)".  I'll be working with our Admin team to get that corrected, but I'm not sure that's the issue.

SCOM is still showing Database Size for the relevant hosts as over 50k records.  I re-checked the host tables, and they are still at or near 0 records.

My only thought is that the database size records are somehow related to the Spool table records, according to https://msdn.microsoft.com/en-us/library/aa578302.aspx:  Regarding Database Size:  If a process is publishing to multiple queues, this counter reflects the weighted average of all the queues.  The problem is I can't figure out how to link the Spool messages back to the original host, only to the current (batching) host.  So, I still don't know where/how the Database Size numbers are calculated.

Thanks,

Rob

Hi Rob,

Do you have any orchestration which runs for days(sequential orch) which collects record of all the messages processed so far?

If yes in that case these messages will have their reference in spool table. Messages only gets deleted from spool when they don't have any reference. Spool table will also have reference of all messages which are suspended, scheduled processing etc.

Also, check the record count in TrackingData Tables in MessageBoxDb tables like- TrackingData_0_x, where x=0,1,2,3,4

Size of these tables also cause Database Throttling. If they are huge in size check if the Tracking host is enabled.

We can give few more suggestions by looking at the output of MBV report, if possible share the same, I would analyze and give my findings.

You can bookmark below link as well.

How to maintain and troubleshoot BizTalk Server databases

Hi Rob

Your dedicated Receive Host is throttling due to the number of messages it has published to the Queue tables(Active, Suspended and Work) of the Subscribing Hosts.

So, lets say you have a SendPort (in a Host called SendHost) that subscribes to the messages published by your ReceiveHost. And there are 20k messages in the SendHostQ published by your ReceiveHost. Furthermore, lets say there's an Orchestration(in a Host called ProcessingHost) that also subscribes to the messages published by some of the ReceiveLocations running under your dedicated ReceiveHost. Let us assume the ReceiveHost has published 30k messages to this ProcessingHost. So, we now have 20k + 30k = 50k messages published to the Subscribing Hosts by your dedicated ReceiveHost. This would cause your ReceiveHost to be throttled by the runtime so that it slows down publishing further messages.

From your scenario description, this is the only possibility. This is a watered down version of the actual logic, but it's very easy to miss.

Thanks,

Arindam

• Edited by Arindam Paul Roy Thursday, April 02, 2015 8:21 PM

Hello All,

I am starting to get a picture of what is happening, and I think it's a combination of your responses.

First, we do have dedicated Tracking Hosts and they are up and running.  Our tracking data is minimal.

I'm going to hold off on submitting the MBV report for right now.  We can revisit that if we need to.

For illustration, our document flow goes something like this:

RECEIVE_HOST -> PROCESSING_HOST -> EDI_BATCHING_HOST -> SEND_HOST

Our Batching Orchestrations are long running.  VERY long running, as in they only stop if we manually stop and restart them.  I have noticed that these batching orchestrations NEVER let go of message references unless they are stopped and restarted.  Hence, our large Spool size of ~270,000 records.

So, if the previous replies hold true, the watered down version of this is something like 270k records / 4 hosts = 65k records which is above the 50k high database size threshold.  Throttling begins.  And because the EDI_BATCHING_HOST is not releasing messages, the RECEIVE_HOST stays throttled indefinitely.   The quick fix is to restart the RECEIVE_HOST, but that is temporary.  A longer term fix would be to reduce the Spool table.

We will be restarting several of the largest offending batches this weekend to see if this holds true.  We should have results on Monday.

A different question:  Should Batching Orchestrations be long running (indefinitely), or should they be somehow automatically restarted to free up the message references they are holding?  This is something that was already setup when I inherited the app, so the batching setup is an unknown for me...but seems wrong.

Thanks,

Rob

Hi Rob

For your scenario, you can consider increasing the Message Count in DB throttling parameter for your ReceiveHost from 50k to 80k-100k.

Since the subscribers are batching orchestrations, the messages would stay back in Spool longer than non-batching scenarios. However, as soon as a batch is released, the messages that were part of the released batch would be deleted from the Spool. It doesn't matter how long your orchestration instance runs for - the lifetime of a message is determined by the batch release criteria, and not by the lifetime of the orchestration instance. Maybe you can review your batch release criteria (from EDI agreement) to understand the lifetime of a message that is part of a batch.

Increasing the Message Count in DB for your ReceiveHost is certainly worth considering for your scenario.

Thanks

Arindam

• Proposed as answer by Arindam Paul Roy 11 hours 44 minutes ago
• Unproposed as answer by Arindam Paul Roy 11 hours 44 minutes ago

A different question:  Should Batching Orchestrations be long running (indefinitely), or should they be somehow automatically restarted to free up the message references they are holding?  This is something that was already setup when I inherited the app, so the batching setup is an unknown for me...but seems wrong.

Thanks,

Rob

In one of my projects we faced a similar issue, we had a sequential convoy which was supposed to run forever. After facing the throttling issue, we contacted Microsoft and found some articles and decided to have an Exit condition from the loop. In our case everyday at 11:55PM Orchestration Instance will exit(by delay shape) and thus all the references of old messages will be deleted.

However, you definitely have option to avoid throttling by increasing the Message Count in DB to a higher value or decreasing throttling delay, but these are all temporary options. Real option will be to figure out how to delete references of old messages from DB.

A different question:  Should Batching Orchestrations be long running (indefinitely), or should they be somehow automatically restarted to free up the message references they are holding?  This is something that was already setup when I inherited the app, so the batching setup is an unknown for me...but seems wrong.

Thanks,

Rob

The lifetime of the batching orchestration instance does not determine how long a message lives in the Spool. You need to look at the batching criteria (say a day, or 1000 transaction sets) to understand how long a message would stay back in the Spool. The orchestration instance may be active for a year or may run indefinitely, that does not mean that sent messages would not be released from the Spool for that duration. As soon as the batch is sent out(as per batching criteria), the constituent messages would be released from the Spool.

Thanks,

Arindam

In one of my projects we faced a similar issue, we had a sequential convoy which was supposed to run forever. After facing the throttling issue, we contacted Microsoft and found some articles and decided to have an Exit condition from the loop. In our case everyday at 11:55PM Orchestration Instance will exit(by delay shape) and thus all the references of old messages will be deleted.

This sounds like something we could do.  How did you trigger the orchestration to start back up? We could do a scheduled script, but I'm curious how you solved this.

Thanks,

Rob

The lifetime of the batching orchestration instance does not determine how long a message lives in the Spool. You need to look at the batching criteria (say a day, or 1000 transaction sets) to understand how long a message would stay back in the Spool. The orchestration instance may be active for a year or may run indefinitely, that does not mean that sent messages would not be released from the Spool for that duration. As soon as the batch is sent out(as per batching criteria), the constituent messages would be released from the Spool.

Thanks,

Arindam

I wish this was the case, but for whatever reason, our batching orchestrations DO hold on to message references even after releasing the batch.  Our batches are scheduled every 10 minutes.  But if you right click on the batching orchestrations in the Admin Console and select "Show Messages", all of the batched messages still show up there.  Thankfully it's only the full batched message, not the individual input messages. 

Which is curious.  The input messages are no longer in the spool, but the batched messages are.  How does that still link to the RECEIVE host?

Hi Rob

Is reporting turned on for this agreement? If yes, can you run the query for Batch Status from Admin Console? That should give you a few pointers.

And when you do a Show Messages for the instance, you are seeing all of the historical batches that were ever sent out by it?

Thanks

Arindam