Friday, January 6, 2017

The hard Purge interval cannot be less than the soft purge interval

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 :

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.