Tracking in BizTalk – Adventures in Software

Getting your Tracking settings right in BizTalk is a balancing act, and especially for Production. All too often, tracking won’t get proper consideration until your integrations have been live for a while, and you start to hit a few problems as your tracking database grows larger. Here I’ll talk through the main factors that affect your tracking database, and offer some useful approaches to getting your tracking in order.

The symptoms

If your BizTalk tracking settings aren’t right, the first sign is normally this nuisance appearing when you try to query the BizTalk Administration Console.

In addition, you might well find that querying the Admin Console in general is very slow, particularly the further back in time you try to query (even if it’s just a few hours).

As the error suggests, the underlying cause is a timeout while querying the tracking database (i.e. BizTalkDTADb). If this is happening to you, the sections below outline the causes and your options for dealing with them.

Adjusting the query timeout

It’s not going to solve any of your underlying problems, but of course you can extend the query timeout if it’s a little low. Mine seems to be 30 seconds, which is perhaps a little stingy. If you really want to cheat, you can change these registry values (one for 32-bit, one for 64-bit, delete as applicable).

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Tracking\ConnectionTimeout
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\BizTalk Server\3.0\Tracking\ConnectionTimeout

The DTA Purge and Archive job

You guessed it, this job clears down old tracking data. In your BizTalk database, you should be able to see the SQL Server Agent job DTA Purge and Archive, which acts on the tracking database BizTalkDTADb. By default, it may not be configured correctly or enabled.

To archive or not to archive? If you open the DTA Purge and Archive job, you can see that it calls stored procedure dtasp.BackupAndPurgeTrackingDatabase, passing it various parameters. Note that if you prefer, you can switch this to dtasp.PurgeTrackingDatabase. As you might imagine, this ditches the “backup” part, so think carefully!

Of course, you could also run these stored procedures as one-offs if you wanted. Here’s how they work. Please note that parameters vary slightly between the two, and also between different versions of BizTalk. If in doubt, check the stored procedure definition in SQL Server Management Studio, where you can see the exact parameters for your install.

PurgeTrackingDatabase takes these parameters:

nHours and nDays: the number of days and hours you want to keep the data for, counting backwards from now (nHours + nDays)
nHardDays: the number of days for the “hard purge”. This must be equal or higher than nDays. The “hard purge” will purge older data even if it’s associated with instances that are still running. The normal purge will only affect completed or terminated instances.
dtLastBackup: Set this to GetUTCDate() if you want to purge anything, otherwise if set to NULL, it won’t.

BackupAndPurgeTrackingDatabase replaces the first three parameters above with slightly different names nLiveHours, nLiveDays and nHardDeleteDays. They do the same as thing as their equivalents in PurgeTrackingDatabase. Additionally, it has the following extra parameters:

nvcFolder: the folder to store .bak backup files
fForceBackup: changing from 0 to 1 will force a backup at the point this job is run

You can find more information on these stored procedures in the Microsoft documentation.

Set your tracking level

This is probably the most important and overlooked step. By default, you might find that you’re tracking way more than you need to be. Think carefully about these types of tracking, and disable what you don’t need. You can always switch them on again to investigate ongoing issues (but changes to tracking will only affect new messages, you can’t get back data you didn’t track).

Global tracking: If you want, you can switch off tracking entirely. Rarely useful in the real world, but see Microsoft’s instructions if you want.
Pipeline tracking: Annoyingly some tracking can only be switched off by pipeline (not ports). This includes the data returned when you query the Admin Console for “Tracked Message Events” on ports. Remember to also check the default pipelines, which only appear under All artifacts in the Administration Console! Different tracking levels (starting from lowest) are: nothing; port start/end times (i.e. the fact the port ran); message send/receive events within the port. Additionally, you can track the bodies of messages (the actual content), which will take up much more space.
Orchestration tracking: Almost the same options as Pipelines above, but additionally you can track shape start/end. This is only used by the Orchestration Debugger, which you shouldn’t need in Production!
Port tracking: Send and Receive Ports allow you track message properties and/or bodies, at a state before and/or after any port processing. Two-way ports have the options doubled up, for the outgoing and incoming messages.

Monitor for orphaned records

Unfortunately, BizTalk can get some tracking records orphaned when things go wrong. These records are not in use, but will never be cleared down by the purge jobs mentioned above.

If you have suspiciously old records in your tracking database, it might be worth running BizTalk Health Monitor. Microsoft offers this free download, and it’s capable of querying for orphaned instances in the DTA (i.e. tracking) database, amongst lots of other health checks in the BizTalk database.

The big caveat is that you can’t run it while BizTalk is running. You have to stop all host instances and the SQL Server agent to run it, so it’s not great for Production. However, some people have reported large build ups of orphaned records, so it may be worth doing. Personally, I’ve never encountered orphaned counts high enough to affect performance at all.

Use Business Activity Monitoring instead

Often, BizTalk tracking can be overkill for Production databases. Instead, consider writing your own Business Activity Monitoring (or BAM) profiles to record only the data you really need, and in a format that actually lines up with your business processes. BAM is BizTalk’s reporting layer. It can be quite fiddly to set up, but often makes a better alternative for keeping an eye on what BizTalk has been doing. You can then bring the tracking levels down to just the essentials.

BAM is way too big a topic to cover here, but I might do an article in future. Consider looking into it if BizTalk seems like a black box in your organisation, and you want to structure your own activity reports.