How to clean up your BizTalk databases

Here are a few scripts / stored procedures that most of the BizTalk newbies would love to know.

These scripts are very useful in a development environment to clean up your MessageBox and Tracking databases.

To clean up the MessageBox

  • First create the stored procedure bts_CleanupMsgBox by running the sql script found in <BizTalk Installation Folder>\Schema\msgbox_cleanup_logic.sqlagainst your MessageBox database(BizTalkMsgBoxDb).
    • Note that your BizTalkMsgBoxDb database will already have a dummy stored procedure by the same name which does nothing, so it is important that you run the above script if this is your first time.
  • Stop all BizTalk services.
  • Reset IIS server (run iisreset from start/run) if you are running any webservices
  • Execute stored procedure bts_CleanupMsgbox on your message box database
  • Execute stored procedure bts_PurgeSubscriptions on your message box database
  • Restart all BizTalk services

To clean up the Tracking data

  • Stop all BizTalk services.
  • Execute stored procedure dtasp_CleanHMData on your tracking database (BizTalkDTADb)
  • Restart all BizTalk services

Word of caution: MSDN article on this has a note that says this is not supported in production. Check it out: http://msdn.microsoft.com/en-us/library/bb727781(v=bts.20).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s