“Effect” is not a good word for it. Maybe “messes up”, “disturbs”, or “kills” is better.
After installing the Cumulative Update (CU) referred to as KB 2429050 everything seemed ok until some of the jobs collecting in/out statistics from the DTA database failed. Actually, they did not fail; they simply reported that no messages had passed through BizTalk. This was, of course, wrong!
If you want the quick fix: scroll to the end.
How does basic tracking work?
As you might know there is something called BAM tracking. BAM is richer, better and more flexible than basic tracking. By default BizTalk tracks all events of messages going in and out of the message box as well as instances of services. All that data is useful for debugging, statistics and to answer the age old question from the people that we develop our applications for: “Has my file passed through BizTalk?”.
In order to make this possible and still not weigh down the message box with data that really should not be there, the data is moved to a separate database called BizTalkDTADb. This database is famous for bloating up, especially it’s log datafile.
The job of getting the data from the message box into the DTA falls to one or more hosts in BizTalk that are marked with “Allow Host Tracking”. Note the word host and not host instance. One host must be configured to handle this job. You cannot configure BizTalk hosts without defining at least one tracking host.
One very good optimization is to configure a host that does all the tracking and to set this host to be the only one handling tracking. MSDN has a good article about it. This way you always have tracking active (since you never need to stop that host) and all the other hosts do not get “bogged down” by tracking overhead.
You can turn tracking off all together if you really want to though. The downside to this is that you will most likely regret the decision.
How do I know if I have a problem?
You might not use the info in the tracking but BizTalk still tracks data. If you have this issue – the data will not be moved from the message box! Your message box will continue to grow and performance will go down.
There is a quick and easy way to find out if you have a problem. BizTalk dumps all failed tracking to a separate table called TDDS_FailedTrackingData in the BizTalkDTADb. This table should be empty if your DTA clean up jobs are running. Just run a simple select * and order descending. Look at the newest row. If the dtLogTime is resent compared to when a message lastly was submitted to BizTalk; you might have a problem my friend.
The table contains the error that caused the event tracking to fail. My error messages was not exactly helpful but it pointed me to a forum post that helped solve the issue. The message reads:
TDDS failed to execute event. Stored Procedure dtasp_MsgOut failed to run. Stored Procedure dtasp_MsgOut Parameter Count and IPersistQueryable Item count not the same, Expected Count: 0, IPersistQueryable Count: 14
Other procedures than dtasp_MsgOut are mentioned in other error messages, like: dtasp_ServiceInsert, dtasp_CtxInsert or dtasp_ServiceEnd.
Perhaps someone other than me can make sense of it.
Another way is to take a look at one of the tables that should contain tracking data. Do a simple select * on the dta_MessageInOutEvents table. Order descending by dtInsertionTimeStamp. The newest row should be fairly resent depending on the load on your system. If the last inserted row was on the day you installed the CU, then you know what caused it.
Yet another way is to look at the DTA database size and compare it to how it looked before you installed the update. If it is much smaller than in should be, then you might have a problem.
Enough! How do I solve the issue?
This solution does not answer why. It only shows you how.
You have to configure a new tracking host with, and this is important, a new name. No joke! If you simply restart the host or delete it and create a new one, then it will not work.
Follow these simple steps (step 7 and 8 are optional):
- Go get your passwords and BizTalk users and groups since you probably have not used them in a while.
- Stop the host instance(s) that handle the tracking.
- Create a new host (not instance) and call it something different compared to the existing one. Remember to check the option “Allow Host Tracking”. Later update: This might not be necessary. Check out the update below.
- Create a new host instance for the host. Depending on your setup, you might need to create more than one. Just make sure that the setup looks like it does for the existing host and host instances.
- Start the new host instance(s).
- Delete the old host. This will automatically delete all host instances.
- Open regedit and do a search for your now deleted host name. Notice that it is not gone from the registry.
- Run the select-query mentioned above on the TDDS_FailedTrackingData. If new data passes though the system and you do not get any more rows here, everything works fine.
Some of you might think that there must be a simpler, better way of doing this. I have yet to find it. If you however have this issue and feel that you do not want to manually update you can always write a script. There are good ones for creating hosts and host instances out there.
Forum user Phil D Smith posted that he managed to recreate a dedicated tracking host with the same name. He claims to have succeeded by
- Moving the tracking to the default host by allowing that on that host.
- Restarted that host (has to be done anyway).
- Stopped the dedicated tracking host instance.
- Deleted the tracking host.
- Created a new tracking host with the original name.
Now I was pretty sure I tried this but I was obviously wrong.
Further research is needed since failed tracking does not seem to be an issue for everyone. There are as always a lot of variables when you compare different setups, we all know this.
I would love feedback! Both about facts that I might have gotten wrong, but also about your setup and your experiences. Most importantly: the answer to the question “why”.