Upcoming DevLive Sessions http://blogs.msdn.com/b/biztalknotes/archive/2013/08/02/upcoming-devlive-sessions-for-biztalk.aspx
DTA purge and Archive job gets created automatically on the SQL server when you configure the BizTalk group pointing to that SQL server.DTA Purge and Archive job does two maintenance operations on the Tracking Database : Purging and Archival of the data.
The detailed description of the DTA purge mechanism is explained in the following link:Archiving and Purging the BizTalk Tracking Database : http://msdn.microsoft.com/en-us/library/aa560754.aspx
You can manually purge the tracking data using the dtasp_PurgeAllCompletedTrackingData stored procedure in case you don't enable this job in the environment.This would require manual intervention and also a downtime as dependent services need to be stopped during this process.Description on How to Manually Purge Data from the BizTalk Tracking Database is explained at http://msdn.microsoft.com/en-us/library/aa561918.aspx .
By default the DTA Purge and Archive job will have the dtasp_BackupAndPurgeTrackingDatabase stored procedure associated with it.
Here is an example of the parameter configuration for the above stored procedure:exec dtasp_BackupAndPurgeTrackingDatabase1, --@nLiveHours 1,0, --@nLiveDays1, --@nHardDeleteDays‘\\server\backup’, --@nvcFoldernull, --@nvcValidatingServer0 --@fForceBackup Soft purge process
If you choose to run the above procedure with nvcFolder parameter configured to point to a UNC share, Any data older than HardDeleteDays interval is eligible to be archived at the next archive and then purged. Default is 30 days.You can set the fForceBackup Soft purge process to archive the data which are being soft purged during every execution.
Archiving is an optional functionality along with the Purging. There are customers who use automatic validation functionality they have reporting requirements on the tracked data.The '.bak' file created by the DTA Purge and Archive job is restored on a different SQL server, can use custom tools to create reports from them.
If you don’t require to Archive the Tracking Data
The exec dtasp_PurgeTrackingDatabase stored procedure does not archive the BizTalk Tracking (BizTalkDTADb) database. Before using this option, be certain that you no longer require archived tracking data.Change exec dtasp_BackupAndPurgeTrackingDatabase to exec dtasp_PurgeTrackingDatabase.
How to Purge Data from the BizTalk Tracking Database using dtasp_PurgeTrackingDatabaseis explained at http://msdn.microsoft.com/en-us/library/aa578470.aspx .
Tables affectedThe stored procedures configured on the DTA Purge and Archive job calls the stored procedure dtasp_PurgeTrackingDatabase_Internal.This stored procedure deletes data from the below tables of the DTADb.dta_CallChaindta_DebugTracedta_MessageInOutEventsdta_RulesAgendaUpdatesdta_RulesConditionEvaluationdta_RulesFactActivitydta_RulesFireddta_RuleSetEngineAssociationdta_ServiceInstanceExceptionsdta_ServiceInstances
How the SP works
Explanation on the actions performed by the stored procedure is clearly explained in the comment section of the stored procedure.I have added the same below for ease:This code is designed to keep infinite schedules from filling our database with information about their execution. MSMQt is handled correctly because we use an activity id for MSMQt. The only thing we need to do here is delete the DebugTrace and CallChain table information as well as the MessageInOutEvents information. These tables contain shape execution and nested orchestration call information, so the orchestration debugger will not be able to give complete information. The InOutEvents table contains information about message flow into and out of the schedule so that will also be deleted.Not only do we check when they completed, but also when they were inserted so that we make sure we dont delete data which was not in the last backup. Also prune rules engine related information.We need to prevent page locks which cause deadlocks so we delete 100 at a time until we have no more work to do on a table
Here is an article which explains about the importance of maintaining your BizTalk database size at a healthy level.Improving the Performance of the Archiving and Purging Process : http://technet.microsoft.com/en-us/library/aa578643.aspx
From the environment supportability standpoint, MS recommend all the BizTalk users to enable the DTA Purge and Archive job in their environment.The parameters that can be modified are the ones which are configurable parameters for the SP’s configured for the jobs.The schedules for this jobs are set to default values which were tested by the product group and is verified to have shown the best results.The BizTalk jobs and internal logic were designed and created by the same team who worked on the SQL engine logic.Written By Jainath V R
Reviewed ByChirag Pavecha
Microsoft GTSC India