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