I have seen most of the issues coming with slow processing of messages in production by Biztalk where the root cause is only due to large size of Biztalk databases.
Though, Biztalk comes with SQL Jobs which maintains the Biztalk databases and keep them at good shape, but if these Jobs are not configured properly or are not running or failing, the performance is bound to degrade with time.
Also, other thing which sometimes we miss out is clearing out suspended instances. There is no way Biztalk would determine what to do with suspended instances automatically and if these suspended instances keep increasing, it will cause Biztalk DB's to bloat, again causing performance degradation.
You can find description on all the Biztalk Jobs from here
Mostly I have seen that 'DTA Purge and Archive' and 'Backup Biztalk Server' Jobs are disabled. Lets look a the importance of these jobs.
a) DTA Archive and Purge Job is responsible for maintaining BiztalkDTA DB. If the DTA DB becomes huge, the Biztalk service responsible for moving tracking data from MessageBox DB to DTA DB takes more time, because of which it has a backward effect causing overall performance issues.
Only reason the DTA DB becomes huge is that DTA Purge and Archive Job is not enabled or not configured properly. The two important parameters for configuring this job are:
Soft Purge: It specifies the duration for which tracking data of completed instances will be stored in DTA DB and prior to this duration will be purged after archiving it first. It is determined by the combination of parameters LiveHours and LiveDays.
Hard Delete: It specifies the duration for which tracking data of incomplete instances or running instances will be stored in DTA DB and prior to this duration will be purged after archiving it first. It is determined by the parameter HardDeleteDays.
Make sure that the Soft Purge is not set to a large value, otherwise DTA Will store tracking data for these many days at a time in DTA DB
b) The backups for Biztalk DB?s taken by Backup Biztalk Server job is only supported way. So I would strongly recommend to use this job only for tacking backups. The Backup BizTalk Server Job creates synchronized backups of all BizTalk Server databases by using full database backups and transaction log backups
There is a very useful utility named as MessageBoxViewer (developed by JP, Thanks to him for making our job easy 🙂 ) which is an excellent tool to find out common issues with Biztalk. You can download the latest version of this utility from here (JP's blog)
This utility takes a snapshot of overall Biztalk configurtaion and its databases and if it found any issues going on Biztalk, it will just mark it as red in Status Report. We should keep running it on Biztalk environments from time to time to get overall health status of Biztalk databases.
I hope it helps! Rest, we are always here 🙂