USE BizTalkDTADb
--select top 10 * from dbo.dta_DebugTrace
select COUNT(*) from dbo.dta_DebugTrace as TotalRowCount

SELECT YEAR(dtBeginTimeStamp) AS Yr,
Month(dtBeginTimeStamp) AS Mo,
COUNT(*) AS [RowCount]
FROM dbo.dta_DebugTrace nolock
GROUP BY
YEAR(dtBeginTimeStamp),
DatePart(mm,dtBeginTimeStamp)
Order BY
YEAR(dtBeginTimeStamp),
Month(dtBeginTimeStamp)

The results show you by year/month how many rows. In most cases, you will find you need to purge data prior the current month. At least on a test system, I can think of any possible use of month old orchestration trace data (or even on Production for that matter).  But if you have long-running dehydrated orhestrations you might need it).

dta_DebugTrace_Counts

Orchestrations write data to these tables each time they run (when Trace is enabled).

Go to SQL Agent on the SQL Server that supports your BizTalk server, check the job entitled: DTA Purge and Archive (BizTalkDTADb)
SQLAgent_Jobs_BizTalk_2010
I take the original code that is there, and copy it to the line below, then comment out the code that is there with the T-SQL comment (two dashes).
Then change the second line to the parameters you want. Follow-that by running the job manually, or set it up to run on a scheduled basis.


--exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --


exec dtasp_BackupAndPurgeTrackingDatabase 24, 30, 32, 'e:\Backup\BizTalkDatabases\', null, 0

MSDN Reference: https://msdn.microsoft.com/library/aa558715.aspx

NOTE: Running the job creates a backup, so if your DTA database is large, and you are short on disk space, you will have to make sure you find disk space for the backup to be written.

If you get odd errors, try running the command directly in a SQL query window, then the a more useful error may be displayed than what you would see in the “View History” of the SQL Agent Job.

dtasp_BackupAndPurgeTrackingDatabase_Errors


The hard purge window cannot be less than the live data window [SQLSTATE 42000] (Error 50000).
The step failed.

After changing the second 30 to 32, I re-ran it, and saw this result:

Successful_Run_of_dtasp_BackupAndPurgeTrackingDatabase

 

The Parms are as follows:
dtasp_BackupAndPurgeTrackingDatabase_storedProc_Parms

 

However, after running it, I still saw many of the same rows there (this is on BT2010 by the way). I did some more research, found Sandro Pereira’s blog and ran the following:


declare @dtLastBackup datetime
set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

Filed under: BizTalk Admin