Issues with Tracking causing high CPU usage on BizTalk SQL Server (Additional Info)

Since the 1st occurrence of this issue (Issues with Tracking causing high CPU usage on BizTalk SQL Server), I have had 2 other clients with similar issues, each with different root causes.  The key finding about these issues is that it is not obvious what the problem is from the initial problem determination, one of the recent issues showed up as a throttling issue for database size, but when running the Message Box Viewer tool, there was no errors that indicated the root cause, but there were warnings around the size of on of the tracking_1_x tables.

The underlying issue with the 2nd occurrence of this issue was a failed upgrade from 2006 R2 to 2009, although the upgrade seemed to work correctly, the user running the upgrade did not have sysadmin privileges on the BizTalk SQL Server, as required in the install instructions.  This lack of permission lead to the service account for the Tracking host not being given permission on the tables and stored procedures that are used doing the movement of tracking data from the message box database to the tracking database.  Once the permissions were update (cross checked permissions from a successfully upgraded 2009 BizTalk SQL Server), tracking data started to be moved from the message box database to the tracking database.

The 3rd occurrence is still under investigation, but the symptoms are the same, High CPU on the BizTalk server and large number of records in the tracking_1_x tables in the message box database.

From now on, one of my 1st checks for a poorly performing BizTalk system will be check the tracking_1_x tables and the TDDS_StreamStatus table.  I will be publishing a SCOM monitor to detect the tracking data not being copied and hopefully a rule for message box viewer that will clearly identify this issue.

Comments are closed.