As BizTalk Server processes more and more data on your system, the BizTalk Tracking (BizTalkDTADb) database continues to grow in size. Unchecked growth decreases system performance and may generate errors in the Tracking Data Decode Service (TDDS). In addition to general tracking data, tracked messages can also accumulate in the MessageBox database, causing poor disk performance.
When you purge data from the BizTalk Tracking database, the DTA Purge and Archive job purges different types of tracking information such as message and service instance information, orchestration event information, and rules engine tracking data.
The age of a tracking data record is based on the time the tracking data was inserted into the BizTalk Tracking database. The DTA Purge and Archive job uses the time stamp to continuously verify whether the record is older than the live window of data. After every live window period, the BizTalk Tracking database is archived and a new archive file is created. At each SQL Server Agent job interval specified by the job schedule, all completed tracking data older than the live window period is purged.
BizTalk Server uses the concept of a soft purge and a hard purge. The soft purge is used to purge completed instances, while the hard purge is only used to purge incomplete instances.
Out of the box it will not work, You must first configure and then enable the job. For instance, you will need to provide a place for your backups to be written.
For example, you can configure the DTA Purge and Archive job to run every 20 minutes, and set LiveHours=1 and LiveDays=0. The first time this SQL Server Agent job runs (T0), it takes a backup of the tracking database by creating an archive and an entry is saved in the database with this timestamp. A successful archive is necessary in order to purge tracking data. If the archive was successful, then all the data associated with the instances that completed over an hour ago is purged. Each time the job runs, completed data over one hour old is purged. On the 3rd run (after one hour), a new archive is created that contains the data for all instances that were inserted into the tracking database in the last one hour segment.
Here is how you would configure the Archive and Purge step in the DTA Purge and Archive job to match the example above:
exec dtasp_BackupAndPurgeTrackingDatabase
1, --@nLiveHours 1,
0, --@nLiveDays
1, --@nHardDeleteDays
'\\server\backup', --@nvcFolder
null, --@nvcValidatingServer
0 --@fForceBackup Soft purge process
The time stamp of the last backup is stored in the BizTalk Tracking database and ensures that data is only purged if it is in the previous archive. For additional reliability, archives are overlapped by approximately 10 minutes. The following figure, based on the example above, shows the soft purge process. Note that the archiving and purging tasks do not necessarily happen at the same time.
Because the soft purge only purges data associated with completed instances, if you have many looping instances that run indefinitely, then your tracking database would grow and these instances would never be purged. The hard purge date allows all information older than the specified interval to be purged except for information indicating a service’s existence. You set the hard purge using the @nHardDeleteDays parameter in the Archive and Purge step in the DTA Archive and Purge job. The hard purge setting should always be greater than your soft purge setting. In other words, @nHardDeleteDays should be greater than the sum of @nLiveHours and @nLiveDays.