DTA Purge and Archive (BizTalkDTADb) job: Procedure or function dtasp_PurgeTrackingDatabase has too many arguments specified. [SQLSTATE 42000] (Error 8144). The step failed.

I normally advise my clients or partners to “religious” follow the order and steps of my BizTalk Server Installation and Configuration guide, an advice that I also take into consideration for myself, not because following the exact order of the steps are mandatory, some of them are but others don’t, but because if you follow that “recipe” you will end up with a successfully BizTalk Server installation better optimized than the default and normal installations without encountering errors.

Despite all of my warning I am also human and make “mistakes” of not following the order of some steps to try to agile the BizTalk Server configuration/optimization process and a few weeks ago, while installing multiple machines in several environments at the same time we encountered a “new” problem after we configured the DTA Purge and Archive (BizTalkDTADb) job. When we try to execute the DTA Purge and Archive (BizTalkDTADb) job it failed with the following error message:

Executed as user: <servicename>. Procedure or function dtasp_PurgeTrackingDatabase has too many arguments specified. [SQLSTATE 42000] (Error 8144). The step failed.

Cause

The error message is very clear “… dtasp_PurgeTrackingDatabase has too many arguments specified…” but my first instinct was to say “Impossible!”. I been using the same job configuration from last 10 years in all my environments successfully so why this is failing?

Then I realize that I recently updated my guide to improving the DTA Purge and Archive (BizTalkDTADb) job to be able to automatically delete orphaned BizTalk DTA service instances.

This is a new extra feature that the BizTalk Product group release with:

• Cumulative Update 1 for BizTalk Server 2016
• Cumulative Update 5 (CU5) for BizTalk Server 2013
• Cumulative Update 6 for BizTalk Server 2013 R2.

The “traditional” contract of the stored procedure is to have six parameters that you must configure:

• @nHours tinyint: Any completed instance older than (live hours) + (live days) will be deleted along with all associated data.
• @nDays tinyint: Any completed instance older than (live hours) + (live days) will be deleted along with all associated data. The default interval is 1 day.
• @nHardDays tinyint: All data older than this day will be deleted, even if the data is incomplete. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged.
• @dtLastBackup: Set this to GetUTCDate() to purge data from the BizTalk Tracking (BizTalkDTADb) database. When set to NULL, data is not purged from the database.

Normally the step will be configured like this:

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

But with the CU’s described above there is an additional parameter that you can use:

• @fHardDeleteRunningInstances int = 0: if this flag is set to 1 we will delete all the running service instances older than hard delete days. By default, this new parameter is set to 0

So now the configuration that I normally use is like this:

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

Note: After you install this update, you must manually update the DTA Purge and Archive job definition to pass the additional parameter @fHardDeleteRunningInstances if you want to clean up running service instances that are older than @nHardDeleteDays. By default, this new parameter is set to 0. This continues the current behavior. If you require the new behavior, set this parameter to 1.

My problem was that I was already using this new configuration without installing any CU in our brand-new BizTalk Server 2016 installation

Solution

Of course, you should review the job configuration to see if you properly set all the stored procedure parameters if the script.

But in our case, Installing the most recent cumulative update solved the issue.