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

<br>

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

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).

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)

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.

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

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

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.

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

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

The Parms are as follows:

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:

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

You can also delete the rows of the DTA_Debug table as follows.
It has a loop to delete only a few rows at a time (you can set that number of course), so as not to fill up the TEMPDB or the log file. It’s based on my prior blog on how to safely do mass deletes in SQL server.

<pre>
use BizTalkDTADb

declare @DaysToKeep int = 700 
declare @RowsDeleted int = 1  -- must set > 0 so the loop will be entered the first time through 
declare @TotalRowsDeleted int = 0 
declare @StartDateTime datetime = DATEADD(d, -@DaysToKeep, getdate())
declare @DateTimeTemp DateTime2

WHILE @RowsDeleted > 0
  BEGIN
         --DELETE TOP (1000) FROM Trace where  trcDateTime <= @StartDateTime 
     DELETE TOP (10000) from dbo.dta_DebugTrace where dtBeginTimeStamp <= @StartDateTime 
         set @RowsDeleted = @@ROWCOUNT 
     set @DateTimeTemp = CAST(GETDATE() as Datetime2 (3) )
     print CAST(@DateTimeTemp as varchar(max)) + ' RowsDeleted=' + CAST(@RowsDeleted as varchar(12)) 
         set @TotalRowsDeleted = @TotalRowsDeleted + @RowsDeleted 
         WAITFOR DELAY '00:00:01';  -- Give SQL a little break 
  END 
SELECT @StartDateTime as StartDateTimeOfDelete, 
       @TotalRowsDeleted as RowsDeleted, (select COUNT(*) from dta_DebugTrace) as RemainingRows, 
       MIN(dtBeginTimeStamp) as Min, MAX(dtBeginTimeStamp) as Max from dta_DebugTrace  

</pre>

More stories and ideas: At my current client, the DTA database was 152GB. That database and it’s log file were taking up about 95% of the disk space on the SQL server machine. The prior BizTalk people had never turned on the BizTalk backups and purge jobs! (Oh my!) The disk on the SQL server was close to be being full (it’s a VM, so I could probably have asked for more).

I couldn’t run the full SQL Agent job called “DTA Purge and Archive (BizTalkDTADb)”. I dug into to it to see what it is running. It has a lot of protection around it about not purging until a back up is made.

Well, I couldn’t make a backup without requesting more diskspace! A lot of diskspace! So after delete the DTA_Trace above, I still had a lot of space being used tables such as:

dbo.Tracking_Parts1
dbo.Tracking_Spool1
dbo.dta_MessageInOutEvents

These each has about 18 MILLION rows in them! I ran the SQL statement below, which runs the purge stored procedure WITHOUT the backup. It ran for several hours! It delete 100 rows at a time, in a loop, similar to the code I provided above.

<pre>exec dtasp_PurgeTrackingDatabase_Internal 
  @dtPruneBeforeDate = '2019/01/01',
  @dtHardDeleteBeforeDate = '2019/01/01',
  @dtLastBackup = '2019/01/01' 
</pre>

You can watch to make sure it doesn’t fill your Log file with this command: DBCC SQLPERF(logspace)

You can check your free space in the main DTA database with this command:

<pre>SELECT DB_NAME() AS DbName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB, 
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') 
             AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 
</pre>

After the purges, I will run the DBCC Shrink command on the DTA database. I was already to run the DBCC Shrink on it’s log file.

This was the shrink command for the log file:

<pre>use bizTalkDTADB 
 DBCC SHRINKFILE (BizTalkDTADb_log, 2000)
</pre>