BizTalk slow? The database might need some cleaning up…

We’ve hade some problems earlier with a very slow BizTalk installation. It could takes minutes (!) to consume messages from MSMQ queues and the message queues can build up pretty quickly if throughput is that bad.

It turned out that the main problem was that the messagebox database was full of stale messages that should have been cleaned up, but wasn’t since the database job MessageBox_Message_Cleanup_BizTalkMsgBoxDb was not scheduled (which it should be).

To see the difference this makes, we can first analyze the size of the databases:

USE [BizTalkMsgBoxDb]
GO
SELECT charindex('log',name), name ,size/128.0 AS Size, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS SpaceUsed, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
    name                 Size             SpaceUsed      AvailableSpaceInMB
0   BizTalkMsgBoxDb        4840.000000    4652.812500     187.187500
17  BizTalkMsgBoxDb_log    4000.000000    38.453125      3961.546875

In this case we have a really big BizTalkMsgBoxDb database, Running the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job makes things a lot better:

    name                 Size             SpaceUsed      AvailableSpaceInMB
0   BizTalkMsgBoxDb        4840.000000     167.562500    4672.437500
17  BizTalkMsgBoxDb_log    4000.000000    3693.523437     306.476563

After this our BizTalk installation has been much quicker and now works as it should.

BTW, if the above does not solve the problem then it may help checking that another database job called “DTA Purge and Archive (BizTalkDTADb)” is also scheduled. If it isn’t then the tracking database will grow indefinitely which is not a good thing at all…

Note: Errors like these are easy to find using the BizTalk MessageBoxViewer tool that I can really recommend. Download it here: http://blogs.technet.com/b/jpierauc/archive/2007/12/18/msgboxviewer.aspx

Changing a solicit-response BizTalk send port into a one-way port

Sometimes you might have the need to change the type of an existing send port in BizTalk, e.g. from solicit-response to one-way. For example, when consuming a Wcf service using “Add generated items”, the binding files that are generated are for solicit-response send ports requiring you to handle the responses. If you don’t, you get the following error which is very unexpected if you’re not prepared:

The published message could not be routed because no subscribers were found. This error occurs if the subscribing orchestration or send port has not been enlisted, or if some of the message properties necessary for subscription evaluation have not been promoted. Please use the Biztalk Administration console to troubleshoot this failure.

What the message says is that we have no subscriber to the response message of the Wcf request, and in many cases (one-way messages) we’re not interested in supplying one.

Modifying the send port to a one-way send port solves the problem, but as there’s no option for doing that in the BizTalk Administration Console we have to rely on a trick:

  1. export the bindings to file
  2. modify the file
  3. import the file back

Look here for the details, directly from the horse’s mouth (Microsoft):

http://msdn.microsoft.com/en-us/library/cc185524(v=bts.10).aspx

In case that link doesn’t work when you read this post (it’s been known to happen ;-)), here are the required modifications:

  1. Find the relevant SendPort in the binding file and change the value of IsTwoWay property to false.
    <SendPort Name="port_name" IsStatic="true" IsTwoWay="false" BindingOption="0">
  2. Comment out the ReceivePipeline and ReceivePipelineData elements inside the SendPort element.

That’s all. Importing the binding file back into BizTalk Administration Console should update the send port. If it doesn’t, try to delete the old port before importing the binding file.

In all, the above procedure is not all that complicated which makes you wonder why there is no option to do this directly in the administration console, doesn’t it? 🙂

/Emil

Property promotion when debatching messages from the BizTalk WCF SQL adapter

Richard Seroter has a great post about how to debatch inbound messages from the WCF SQL adapter in BizTalk 2009/2010: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/

The problem is that when polling a database table for new data, we get a single message containing all matching rows. In most cases you probably want to debatch this message into separate message, one for each row, and it’s actually rather easy (see Richard’s post for the details).

There seems to be one problem though, namely that property promotions in the incoming schema (which is created automatically if you’re using typed polling) doesn’t work. One solution for that is to create a separate intermediate schema with the required promotions and transform the incoming messages into that schema using the “inbound maps” setting on the receive port. Note that the map’s source schema should be the top TypedPollingResultSet0 element of the generated schema, not the one below the TypedPolling element, since the transformation is done after debatching takes place.

As an added bonus you don’t mess up your message flows with “TypedPollingResultSet0” message types, that are rather non-descriptive. A message type name of “RaindanceNotification” (or whatever you name the intermediate schema) is much more communicative.

/Emil