How to Purge BizTalk MessageBox DB

If DTA Archive and Purge SQL Server Job does not work , your BizTalk message box db will start to grow as it runs and its size can grow dramatically.

At this point if tracking data is not important for you and if you want to purge all data in MessageBoxDB you should follow below steps.

NOTE: It's important to stop all BizTalk applications and host instances before starting purge operation as this operation will clear all data in the message box.

1. Start --> Run --> IISRESET –stop (That will stop IIS server. This will prevent any IIS Based applications to initiate BizTalk )
2. Open BizTalk Server 2006 Administration Console.
3. Click BizTalk server 2006 --> BizTalk Group
4. Click refresh button or Click F5.
5. Notice that there is no instance in any of the list in the screen.
This step is crucial. All values in this screen must be zero. Otherwise this operation will make us lose messages. Keep refreshing if there is Active instances. Wait for active instances to finish.
NOTE: If there are messages in suspended Service instances, there messages must be analysed to take necessary actions

6. Click BizTalk server 2006 --> BizTalk Group -->Platform Settings --> Host Instances
7. Right click each Host Instances and click STOP
8. Open SQL Server Query Anayzer
9. Connect to SQL Server where BizTalk DB resides and select ‘BizTalkMsgBoxDB’
10. Create the stored procedure bts_CleanupMsgBox by running the sql script found in \Schema\msgbox_cleanup_logic.sql against 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.
11. Execute stored procedure bts_CleanupMsgbox on your message box database
12. Execute stored procedure bts_PurgeSubscriptions on your message box database
13. Shrink ‘BizTalkMsgBoxDb’ both log and data file.
14. Note that size changes after shrink.
15. Right click all Host instances and click START
16. Start --> Run --> IISRESET -START