locked
What cleans out the dta_PortName table in the BizTalkDTADb database? RRS feed

  • Question

  • In the BizTalk Admin console, when I go to Tracked message events and select Port Name in the query expression field name, the drop down of ports to select is filled with what appears to be every port that's ever been created on the BizTalk system even though at least 80% of those ports no longer exist, or at least are no longer visible in the All Artifacts Send or Receive ports.  This is a dev system so ports are created and deleted regularly but it appears nothing clean up those port names.

    I found a matching list of the names in the dta_PortName table so it would appear that's what populates the drop down.  All of the SQL Server Agent jobs appear to be configured correctly and running successfully.  Is there something else that can or should be cleaning non-existent ports out of that table so they no longer appear there or something else that might still be stuck pointing to deleted ports causing them to remain there?

    Thanks,

    Troy

    Friday, December 4, 2015 2:49 PM

Answers

  • Cleaning the Port table, and a bunch of others is not part of the normal Backup/Purge processes.  So, yes, Port names will continue to accumulate forever.

    As slow moving reference tables, this will never cause any problems other than the mis-matched list as you have seen.

    In a DEV setup, or part of very controlled maintenance, you can run the dtasp_CleanHMData stored procedure which clears out everything.  This is similar in scope to the bts_CleanupMsgbox stored procedure you can run on the MessageBox.

    Be careful as these operations are irreversible outside of a database restore.

    • Marked as answer by TryinHard2 Friday, December 4, 2015 6:06 PM
    Friday, December 4, 2015 3:28 PM
    Moderator

All replies

  • Hi Troy,

    Thank you for posting in MSDN forum.

    If your BizTalk related SQL jobs are configured properly and running then I would suggest to run BHM and see if any unexpected issue logged. 

    You can download BizTalk Health Monitor v3.1 

    Please have a look into article Overview of BizTalk Health Monitor (BHM)

    As your system is DEV system so you can terminate the orphaned messages using BizTalk Terminator 2.2

    Using BizTalk Terminator to resolve issues identified by BizTalk Health Monitor (BHM), MsgBoxViewer, and the BizTalk 2010 Monitor BizTalk Server Job

    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, December 4, 2015 3:21 PM
    Moderator
  • Cleaning the Port table, and a bunch of others is not part of the normal Backup/Purge processes.  So, yes, Port names will continue to accumulate forever.

    As slow moving reference tables, this will never cause any problems other than the mis-matched list as you have seen.

    In a DEV setup, or part of very controlled maintenance, you can run the dtasp_CleanHMData stored procedure which clears out everything.  This is similar in scope to the bts_CleanupMsgbox stored procedure you can run on the MessageBox.

    Be careful as these operations are irreversible outside of a database restore.

    • Marked as answer by TryinHard2 Friday, December 4, 2015 6:06 PM
    Friday, December 4, 2015 3:28 PM
    Moderator
  • Thank you for the additional information.  It doesn't look like the obsolete port names are flagged as any kind of an issue in the BizTalk Health Monitor that can be corrected.

    At this point it isn't enough of an issue that I want to execute the dtasp_CleanHMData stored procedure for a larger cleanup.

    I was really looking to make sure that shouldn't be expected cleanup in one of the SQL jobs that maybe wasn't configured or running properly and your replies helped with that.

    Thanks,

    Troy

    Friday, December 4, 2015 6:06 PM