Help my BizTalk databases are growing!

The BizTalk Event in Italy was a blast and I had a great time. Today a day after I arrived home I got intrigued by a scenario on the BizTalk forums. The scenario described a situation that BizTalk databases are growing larger even though the BizTalk database jobs are running correctly (see also database script on MSDN Code Gallery). The execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance. Experienced BizTalk professionals know that all the BizTalk SQL Server Agent jobs except the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job should be enabled and running successfully. Besides this job the other jobs should not be disabled!
By default, the following BizTalk jobs aren’t configured and enabled upon installation.
So as a BizTalk pro you need to configure these according to your requirements for backups, and purging. To configure the first two jobs, see this post from fellow MVP Sandro Pereira where you will find enough detail and background information. The last job is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Therefore, it is recommended that you disable this job.

Now having the jobs running properly is one thing yet there can be more causes to uncontrolled growth of the databases like:
Below you will find more detail on probable causes of uncontrolled growth of the BizTalk databases:

Excessive suspended message or service instances

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port. A proliferation of suspended messages and service instances may cause the BizTalk Server Messagebox database to increase in size beyond what would be expected during normal operations.
BizTalk Server accommodates termination and removal of these instances by using the Group Hub page in the BizTalk Server Administration Console or through the use of the Terminate.vbs script. Through group hub page you can remove them all or selectively (see this post by fellow MVP Ben Cline).

Zombies or orphaned message can be another cause for increase of suspended messages of service instances (this script from Tord G. Nordahl can help you find information on these kind of messages). An orphaned or zombie message is a message that does not have an associated service instance, typically because the service instance has terminated before the message was received. An orphaned or zombie service is a service that does not have any associated messages. Again the terminate script can resolve this issue.

Disk failures

Important for SQL Server is the disk configuration. A wrong configuration could easily lead to disk failures. If one disk fails the disk contention on the other can increase (see also this post). Failure can be related to I/O errors (which cannot be identified through the event log). This can be prevented by performing a disk I/O simulation using SQLIOSim. To prevent disk failures is one thing, but how do identify them. You can look into SQL Server error logs, or you can use SCOM to monitor for failures.

High levels of tracking

By default, tracking is enabled on the default host. BizTalk requires the Allow Host Tracking option be checked on a single HOST. When tracking is enabled, the Tracking Data Decode Service (TDDS) moves the tracking event data from the BizTalk Server MessageBox database to the BizTalk Server tracking database. If no BizTalk Server hosts are configured with the option to Allow Host Tracking or if the tracking host is stopped, then TDDS will not run and the TrackingData_x_x tables in the BizTalk Server MessageBox database will grow unchecked. Therefore, a dedicated BizTalk Server host should be configured with the option to Allow Host Tracking (see also Configuring a Dedicated Tracking Host).

BizTalk Server throttling

There are lot of scenarios because of which Host throttling can occur. It can be because of Low Process Memory, high database size,high thread count and some more. Now the BizTalk Server host throttling mechanism continually monitors for a throttling condition, calculates the severity of the throttling condition, and applies host throttling progressively depending on the calculated severity. The throttling mechanism moderates the workload of the host instance to ensure that the workload does not exceed the capacity of the host instance or any downstream host instances.
Throttling protects BizTalk from completely shutting down. High database size can cause throttling and is an undesired effect. Growth of databases can be caused by fact that more messages are coming into BizTalk than going out (see post by Tord G. Nordahl). To mitigate this problem, see section Throttling condition triggers, actions, and mitigation strategies of How BizTalk Server Implements Host Throttling.

Poor SQL Server Performance

BizTalk Server makes a large number of short, very quick transactions to SQL Server within one minute. If the SQL Server cannot sustain this activity, you may experience BizTalk Server performance issues. Monitor the Avg. Disk sec/Read, Avg. Disk sec/Transfer, and Avg. Disk sec/Write performance monitor counters in the PhysicalDisk performance object. The optimal value is less than 10 ms (milliseconds). A value of 20 ms or larger is considered poor performance. You can use Windows Performance Monitor to monitor these counters. With the BizTalk Server 2010 Performance Optimization Guide that contains prescriptive guidance for optimizing BizTalk Server performance you can try to resolve poor performance.

Network latency issues

SQL Server is one of those few applications that is very sensitive to disk and network latency. If your SQL Server is waiting around for disk or network they will start to complain. To mitigate the impact of network latency on the database throughput, you should consider using gigabit network cards and increase of network bandwidth.
Some of the information found here is from MSDN, TechNet Wiki (i.e. Database Survival Guide) and blogs from fellow MVP’s and BizTalk community members. The health of the BizTalk Server databases is critical for having a stable, robust and sustainable BizTalk infrastructure. There are also some tools that can help in identifying the problem for having uncontrolled growth of the BizTalk databases.

The MsgBoxViewer tool is useful for troubleshooting, because it provides an HTML report that has detailed information about table sizes and the row count. The report can also help determine whether BizTalk Server is throttling. Additionally, the tool provides a snapshot of the BizTalk Server databases and the BizTalk Server configuration. When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, and then review the generated HTML report for any problems. The Summary section lists warnings in yellow and potential problems in red (see section Troubleshooting of How to maintain and troubleshoot BizTalk Server databases).

You can also check you BizTalk deployment by using the BizTalk Server Best Practices Analyzer V1.2. This tool examines a BizTalk Server 2006, BizTalk Server 2006 R2, or BizTalk Server 2010 deployment and generates a list of issues pertaining to best practices standards for BizTalk Server deployments. There are other tools available that can help you test you SQL Server environment and able to tune it, see SQLCrunch.

There are a lot of factors that can cause BizTalk databases to grow and result in a poor health of your overall BizTalk environment. With the information in this post I hope you have a number of tools to either prevent or solve this issue.

Cheers!

- Steef-Jan

Labels: , ,