Cleaning up your tracking database

Published 19 July 04 09:59 PM | christof claessens
 
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!!  In addition, I can't imagine any really bad "worsed case scenario"...
 
To cleanup the tracking database:
  • If you've never done this procedure before, first do this:
    • Open up SQL Server SQL Query Analyzer
    • Connect to the BizTalkDTADb database
    • "File - Open", and go to the BizTalk Server SDK directory and look for the Database Maintenance subdirectory
    • Select the "Purge_DTADB.sql" SQL script file
    • Hit F5
    • 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.
  • To execute the procedure:
    • Select: "File - New - Blank Query Window"
    • Type following statements in order to cleanup *all* tracking data before today:
DECLARE @Today datetime
SET @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:
    • Open HAT
    • Select: "Reporting - Find Message"
    • Try selecting a schema
    • 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.)
Filed under:

Comments

# Robert Rijsdijk said on July 19, 2004 06:21 PM:

Hi Christof ,

Isn't so that BizTalk Server 2004 default stores the Tracking Information in the MessageBox ? There should be a Job or DTS package that cleans up the Tracking Information in the Messagebox and copy it to the Real Tracking Database. When the MessageBox is getting more information the Performance is getting worser and worser.

Robert.

# dann said on July 28, 2004 12:16 PM:

How about achieving the Tracking DB data before one specific date? for example, I would like to save all the inbound/outbound messages into files between 7/1 and 7/30?
dann.

# Christof said on July 30, 2004 05:03 PM:

Robert,

you're right, this however was not the topic I discussed in this entry. Once the data is in the tracking database, it is available for querying. If you would like to archive or clean that data, the described procedure applies.

Best regards!
Christof

# Christof said on July 30, 2004 05:07 PM:

Dann,

you'll need to dive into the SP's source I'm afraid. Thanks for the comment. The scenario you describe is very valid!

Best regards,
Christof

# Lee said on August 4, 2004 02:24 AM:

It is not out yet, but for those of you reading this thread, there will be more guidance and support for archiving and purging your tracking database shortly in a more supported form than just an SDK sample. I will post more information on my blog when it is available.

Dann,
As to your comment about saving message bodies to a file, you only have access to the message bodies if you tracked them. If you did track them and now want to access them, along with the existing WMI apis which we shipped in RTM, we recently released a QFE which included a new component for accessing the message bodies which you tracked. Using this API you could save these out to file. Not sure if this is really what you want, but it is there. This will hopefully be made easier in the next release. I'm working on it.

Robert,
It is true that there is a service which moves and parses tracking data out of the messagebox and puts it into the Tracking Database. This service (called TDDS) runs within the host marked for tracking (a setting in admin MMC when creating the host). If you do not have this running and tracking data grows in the messagebox, it will eventually cause a perf degradation, so you should keep this running.

Thanks
Lee

# Raj said on November 30, 2007 05:13 AM:

I tried using the script provided in the SDK it failed while creating the procedure giving error dta_debug dta_DebugTrace does not have dtTimeStamp column, after i changed this and created the procedure the execution failed giving below error message --

Server: Msg 208, Level 16, State 1, Procedure dtasp_PruneTrackingDatabase, Line 11

Invalid object name 'dta_MessageInstances'.

Server: Msg 266, Level 16, State 1, Procedure dtasp_PruneTrackingDatabase, Line 77

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Can you please guide?

Leave a Comment

(required) 
(required) 
(optional)
(required)