On one of the BizTalk env(non prod) there arouse a space crunch scenario which was call for purging the data from BizTalk tracking database(DTA DB).(dtasp_BackupAndPurgeTrackingDatabase is not configured on this env)
So do that, we decided to purge/delete only those instance which are completed and are older than 30 days and incomplete instances of only past day.
Note: No archiving was needed so instead of dtasp_BackupAndPurgeTrackingDatabase we used dtasp_PurgeTrackingDatabase
Thus the parameters set were:
@nHours tinyint(Any completed instance) :0
@nDays tinyint(Any completed instance) : 30
@nHardDays tinyint(Any including incomplete instance): 1
@dtLastBackup(Set this to GetUTCDate() to purge data): CurrentDateTime
For more details on parameters : https://msdn.microsoft.com/en-us/library/aa578470.aspx
And when started the job, it failed at second step and to check the reason went through history and found the error as below
The error says that 3rd parameter should be greater than 2nd parameter -- for me it is still puzzling and will try to get it understood by product team. But to proceed with purging went ahead and changed the parameters
And started the job and waiting for some time the job was successfully executed
Note: Even now the database size remain same -- here we have to select the DB and Shrink it. This actually forces to release the unused space from DB and as can be seen from below snap - around 20 GB data was purged.
Original Post: http://tech-findings.blogspot.in/2017/01/the-hard-purge-interval-cannot-be-less.html