As part of our regular BizTalk Health Check service that we do for a client, we had an issue where the CPU usage on the BizTalk SQL Server kept increasing over the past few months. I looked at all the usual culprits for this increase in CPU usage on the SQL Server but none of those seemed to be the cause. I started looking a bit deeper into the BizTalkMsgBoxDb with the help of MsgBoxViewer and found that the tracking data from the trackingdata_1_X tables was not being moved to the DTA database. I then checked to make sure that the tracking host was running and it was, it then became a bit more puzzling, the trackingdata_0_X tables were being move to the BAMPrimaryImport, so part of the tracking was working.

After speaking some more with the client, they mentioned that they had run the BizTalk Message Box clean-up stored procedure (knowing it was unsupported in production) more than once in the past year, and the problem was getting worse in the last couple of months. I then started digging a bit deeper into exactly how the tracking data gets moved from the BizTalkMsgBoxDb to the BizTalkDTADb and found that there is a table in the BizTalkDTADb that keeps track of the last Sequence Number moved from the BizTalkMsgBoxDb tracking_1_X table to the BizTalkDTADb. I then looked at the current number in this table (TDDS_StreamStatus) and they were not even close to the Sequence Numbers in the trackingdata_1_X table in the BizTalkMsgBoxDb.

After fixing this Sequence Number mismatch the tracking data started to move as expected and over the next hour or so the CPU usage on the BizTalk SQL server dropped significantly low and back to what I would have expected on a BizTalk system with this amount of load.

The tools that can be used to fix these sort of issues it the BTSTerminator and it can be found along with other BizTalk Tools here: