Cleaning up Biztalk tracking database

Tracking may tend to grow fast, depending on your how intensive your needs are. Out of the box, BizTalk Server does not offer any tools to "autoclean" the tracking database. However, there's a sample in the BizTalk Server SDK directory exactly targetting the subject. Although it's just a sample, I've never seen this fail and it seems to me that the given SQL script is really high quality coded!!

To cleanup the tracking database:
If you've never done this procedure before, first do this:

1. Open up SQL Server SQL Query Analyzer
2. Connect to the BizTalkDTADb database
3. "File - Open", and go to the BizTalk Server SDK directory and look for the Database Maintenance subdirectory
4. Select the "Purge_DTADB.sql" SQL script file
5. Hit F5
6. You should see a message "Creating stored procedure dtasp_PruneTrackingDatabase", in addition the status bar below should mark the operation as: "Query batch completed".You have just created a new stored procedure called "dtasp_PruneTrackingDatabase". This procedure allows you to prune the tracking database. It takes only one single parameter: @PruneBeforeDate. Executing this SP causes the tracking data to be purged that was older then the given (@PruneBeforeDate) date. So, let's try this.
7. To execute the procedure:
   Select: "File - New - Blank Query Window"
   Type following statements in order to cleanup *all* tracking data before today:
   DECLARE @Today datetimeSET @Today = GETDATE()EXEC dtasp_PruneTrackingDatabase @Today

Depending on how much data is to be purged, after a while the Stored Procedure will notify you with its results. Possibly you may see a message in red, commented as: "Duplicate key was ignored." This is fine and is an expected result.

To verify this:

1. Open HAT
2. Select: "Reporting - Find Message"
3. Try selecting a schema
4. If you've cleaned everything, you should not see any schemas anymore... (Only schemas here should correspond to messages that were tracked, áfter the @PruneBeforeDate date parameter.)

If Tracking database is too huge(> 3 GB), it might be better to truncate the complete content in the tables, rather than trying to clean up one row at a time.

Macros for constructing filenames using Biztalk Send port

Probably every demo of BizTalk 2004 you'll see uses a Send File Port that sends messages to a directory and most of the time these files will have a GUID as a filename, so each message sent will have a different name. The solution is to use "MessageID" macro in the URI property of the Sender Port. So the URI property could look like “c:\temp\out\%MessageID%.xml“. Everything between the percentage signs (including themselves) will be replaced by the GUID which is of course unique.

The files in which the files send handler writes messages can be created dynamically using a predefined set of macros. Before creating a file on file system, the files send handler substitutes all the macros in file name with their individual values. Several different macros can be used in one file name. The file name macros can be used while configuring the file send handler in BizTalk Explorer, or using the Explorer object model.


Below are the list of Macros that could be used for file send ports to generate meaningful names, rather than just a GUID.

• %datetime% : Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508)
• %datetime_bts2000% : UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
• %datetime.tz% : Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
• %DestinationParty% : Name of the destination party. The value comes from message the context property BTS.DestinationParty.
• %DestinationPartyID% : Identifier of the destination party (GUID). The value comes from the message context property BTS.DestinationPartyID.
• %DestinationPartyQualifier% : Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
• %MessageID% : Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
• %SourceFileName% : Name of the file from where the File adapter read the message. The file name includes extension and excludes the file path, for example, foo.xml.
• %SourceParty% : Name of the source party from which the File adapter received the message.
• %SourcePartyID% : Identifier of the source party (GUID). The value comes from the message context property BTS.SourcePartyID.
• %SourcePartyQualifier% : Qualifier of the source party from which the File adapter received the message.
• %time% : UTC time in the format hhmmss.
• %time.tz% : Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).