description | title | ms.custom | ms.date | ms.service | ms.reviewer | ms.suite | ms.topic |
---|---|---|---|---|---|---|---|
Learn more about: How to Manually Purge Data from the MessageBox Database in a Test Environment |
How to Manually Purge Data from the MessageBox Database in a Test Environment |
06/08/2017 |
biztalk-server |
article |
When running [!INCLUDEbtsBizTalkServerNoVersion] in a development or test environment, data that is stored in the MessageBox database is not usually business critical "live" data and therefore may be deleted. In these scenarios, you may need a "quick and dirty" method for purging data from the MessageBox database. Follow the procedures in this topic to manually purge data from the MessageBox database using the bts_CleanupMsgbox stored procedure.
Note
You should only perform these steps in a test environment. Manually purging the BizTalk MessageBox database in a production environment is not supported.
-
Stop any instances of the BizTalk service from the Services console.
-
If you are running any adapters in isolated hosts (for example HTTP, SOAP, or WCF), restart IIS by running the IISRESET from a command prompt.
-
Shut down any custom Isolated Adapters that are running.
-
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
-
In the Connect to SQL Server dialog box, select the SQL server and the appropriate authentication method, and then click Connect.
-
In the Available databases drop-down list, select the BizTalk Messagebox database (BizTalkMsgBoxDB by default).
-
Click the New Query icon on the toolbar.
-
Open the msgbox_cleanup_logic.sql file from SQL Server Management Studio. The msgbox_cleanup_logic.sql file is located in the [!INCLUDEbtsBiztalkServerPath]Schema\ directory of the BizTalk Server computer.
-
Click the Execute Query icon on the toolbar to run the script to create the bts_CleanupMsgbox stored procedure. The bts_CleanupMsgbox stored procedure can then be viewed in the list of stored procedures as dbo.bts_CleanupMsgbox.
-
Click the New Query icon on the toolbar.
-
Paste the following command into the new query window:
exec bts_CleanupMsgbox
-
Click the Execute Query icon on the toolbar to run the bts_CleanupMsgbox stored procedure.
[!IMPORTANT] Do not run the bts_CleanupMsgbox stored procedure on a production server that is running [!INCLUDEbtsBizTalkServerNoVersion]. You should only run the bts_CleanupMsgbox stored procedure in a test environment. Running the bts_CleanupMsgbox stored procedure in a production environment is not supported.
-
Restart BizTalk services as needed.
The following considerations apply when running the bts_CleanupMsgbox stored procedure:
-
If you install a hot fix onto your test system that updates the BizTalk database schemas, the hot fix may overwrite the bts_CleanupMsgbox stored procedure with an empty version of this stored procedure. In this case, you will need to follow the procedures outlined in this topic to recreate the bts_CleanupMsgbox stored procedure.
-
If you create a new MessageBox database, the bts_CleanupMsgbox stored procedure will be empty and you will need to follow the procedures outlined in this topic to recreate the bts_CleanupMsgbox stored procedure.
-
Use of the bts_CleanupMsgbox stored procedure is not supported on a production system. This stored procedure will delete all of the data in your MessageBox database.