BizTalk 2009 RTM Upgrade Gotchas – Tracking Data

Originally posted by Nick Heppleston at:

I’ve started to play around with my new BizTalk 2009 installation following my upgrade fromBizTalk 2006 R2 and I’ve noticed a few quirks that you may need to watch out for, following an upgrade.

Tracking Data

The new Admin Console comes with a bunch of new queries relating to your tracking data, including the ability to view Tracked Service Instances and Message Events:


Drilling into the Tracked Message Events view presents the usual Query Results, displaying tracked message events from the BizTalkDTADb dta_MessageInOutEvents and dta_ServiceInstances tables among others:


However, drilling further into an individual message presents the following error dialog:


The message was not found in the Message Box or the Tracking databases. This may be caused by one of the following conditions: (1) message tracking is not enabled; (2) the message(s) is no longer referenced by a running or suspended service instance; (3) the Message Box tracking tables have been automatically purged; or (4) the SQL Server agent is not running on the Message Box servers. (Microsoft.BizTalk.Administration.SnapIn)

It would appear that when the upgrade ‘upgrades’ the tracking databases, some have their data truncated (possibly to allowing the structure of the table to be changed) while others are left untouched. In the above case, tracked data written to the dta_MessageInOutEvents and dta_ServiceInstances tables remains untouched, while data written to the tracking_spool1 and tracking_spool2 tables is truncated (double-clicking a query result item causes the BizTalk Admin Console to issue a call to the ops_LoadTrackedMessages stored procedure, passing in the GUID of the MessageId in question; the stored proc in turn queries the two tracking spool tables and no data is returned).


Don’t rely on your tracking data being available after an upgrade to BizTalk 2009; take a backup of the data before you begin the upgrade and query that data in an offline mode.

Reblog this post [with Zemanta]

Why Archive and Purge when you can just Purge?

In BizTalk 2004 SP2, the BizTalk Team gave us the Archive and Purge SQL Server Maintenance Job for managing the size of the Tracking Database. This was a great tool and really took away some of the admin headaches in maintaining this particular database.

The job allows administrators to archive olde tracking data and verify the integrity of the backup before purging from the live database (for detailed instructions, see the MSDN website). This is a good, pro-active practice for the health of any BizTalk environment, in that:

  • BizTalkDTADb database growth is constantly checked, allowing the TDDS service to run effectively, thereby maintaining a healthy Message Box.
  • Maintaining the size of the BizTalkDTADb data and log files ensures that the database doesn’t just eat all of your available disk space.
  • The tracking data can be restored in a dedicated OLAP environment, allowing reports to be run without affecting the live BizTalk environment.

However, imagine a scenario where you don’t want the archived data – you simply want to purge. Where that’s the case, you can either manually run a truncate on the BizTalkDTADb (see my posts detailing how to do this under BizTalk 2004 or BizTalk 2006 – there are some subtle differences), or run the hidden admin gem dtasp_PurgeTrackingDatabase (a stored procedure used by the dtasp_ArchiveAndPurgeTrackingDatabase which just purges) on a scheduled basis, so you no longer need to worry about manually purging.

Configuring the dtasp_PurgeTrackingDatabase Stored Procedure

The Purge stored procedure is used in a very similar manner to dtasp_ArchiveAndPurgeTrackingDatabase, taking 4 parameters:

  • Live Hours – Any completed instance older than the live hours + live days…
  • Live Days – …will be deleted along with all associated data.
  • Hard Days – all data older than this will be deleted. The time interval specified here should be greater than the live window of data.
  • Last Backup – UTC Datetime of the last backup. When set to NULL, data is not purged from the database.

As an example of its usage, if you wanted to purge any tracking data older than two hours and hard-delete any data older than one day, you would use the follow T-SQL:

–– Change to the BizTalk Tracking database.

–– Prune tracking data older than two hours.
SET @dtLastBackup = GetUTCDate()
EXEC dtasp_PurgeTrackingDatabase 2, 0, 1, @dtLastBackup

The @dtLastBackup parameter is used to ensure that records that have not been backed-up are not deleted by the purge procedure, so we set it to the current UTC date/time to ensure that whatever live hours/days you specify, records are deleted. I’m not too sure why the development team included this as a parameter: the procedure is a wrapper that calls the dtasp_PurgeTrackingDatabase_Internal (which is also called by the dtasp_ArchiveAndPurgeTrackingDatabase procedure) so it could have been included in that wrapper given that it is always defaulted to the current UTC date/time during purges!

One other thing to note is that the wrapper script also modifies the prune before date (the date that is built by the live hours/days specified): this date date is tweaked to remove ten minutes to ensure redundancy in the remaining data.  In the example used above, rather than keeping two hours of data, there will in fact be two hours and ten minutes once the code has been run.

Using the dtasp_PurgeTrackingDatabase Stored Procedure

To start using this procedure, I would suggest that you first truncate your database, then configure the SQL Server Agent job to run every X hours/days depending on your environmental requirements. I suggest initially truncating your database to ensure the first run of the job doesn’t take several hours!

Further details about the dtasp_PurgeTrackingDatabase stored procedure can be found online at MSDN.

Truncating the BizTalk 2004 Tracking Database

This post relates to truncating the BizTalk 2004 Tracking Databases, for BizTalk 2006, see

Update: I’ve had a few questions as to why the views are dropped: these views use the SCHEMABINDING option – creating a view with SCHEMABINDING locks the tables being referred by the view and prevents any changes that may change the table schema, it also appears to stop the tables from being truncated. Hence the views are dropped, the tables truncated and the views re-created. Note: This functionality only appears to be the case in SQL Server 2000, 2005 does not require the views to be dropped.

I’ve noticed that the link I posted to Mike Holdorf’s blog back on my
Host Tracking and the BizTalkMsgBoxDb post no longer works – it would appear that Mikes domain no longer exists.

For posteriety (and because I’ve just had a customer ask me how to do this and I couldn’t remember), here is the script in full:

use BizTalkDTADb

— Drop the Views (before you perform this, ensure you take copies of these views!)
Drop View dtav_ServiceFacts
Drop View dtav_MessageFacts
Drop View dtav_FindMessageFacts

— Truncate the necessary Tables
Truncate Table dta_ServiceInstances
Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_DebugTrace
Truncate Table dta_CallChain
Truncate Table dta_MessageInstances
Truncate Table dta_MessageInOutEvents
Truncate Table dta_MessageFieldValues
Truncate Table dta_MessageFields

— Recreate the dropped views from your own environment.

Reblog this post [with Zemanta]