Latest Tutorials

test

BizTalkBill's Ramblings

Issues with Tracking causing high CPU usage on BizTalk SQL Server

Oct7

Written by:10/7/2011 11:00 AM

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.

Otherwise, look at the lowest number in the tables in BizTalkMsgBoxDb and then set the value in the BizTalkDTADb streamstatus table to one less and restart the tracking host, have backup of both databases before you start.

By Bill Chesnut on 2/12/2013 10:07 PM

Re: Issues with Tracking causing high CPU usage on BizTalk SQL Server

Hi,

Thank you so much for your quick and very helpful reply..Tracking data is now appears in DTA Db.