You are here:   Home
  |  Login

BizTalkBill's Ramblings

Issues with Tracking causing high CPU usage on BizTalk SQL Server

Oct 7

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

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:


3 comment(s) so far...


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


i am also facing the same issue (sequence number of Tables) with Tracking data that is not moving from BizTalkMsgBoxDb-trackingdata_1_X to BizTalkDTADb-TDDS_StreamStatus Table..

Can you please help me in how to fix Sequence number..

By Harmeet Singh on   2/12/2013 5:55 PM

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

download the BizTalk Terminator from here, it has a function to rest the tracking stream tables.

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


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

By Harmeet Singh on   2/13/2013 4:02 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel 



MCP.gif MCT.gif MCPD(rgb)_506.gif