If the BizTalk Message Box database (BizTalkMsgDb) grows too large, performance of the database subsystem will reduce. As a rule of thumb, the BizTalkMsgDb should never grow larger than 5 Gb for large systems with long running transactions. For high-volume environment with no long running transactions, the BizTalkMsgDb size should be much smaller than 5Gb.
Would your message box have already grown too large and the system become unresponsive, you might want to clean up the BizTalkMsgDb database manually but keep in mind that:
- The BizTalk Server must be taken down during the procedure.
- All existing messages will be purged and lost.
There is an article in the MSDN documentation that explains in details how to manually purge data from the Message Box Database.
To make a long story short, here is step by step summary:
- Stop ALL BizTalk service host instances from the Services console.
- Restart IIS by running IISRESET from the command prompt if you are running any adapters in isolated hosts (for example HTTP, SOAP, or WCF).
- Execute the stored procedure bts_CleanupMsgbox on your message box database. If the stored procedure does not exist, create it by running the sql script “msgbox_cleanup_logic.sql” found in the directory “\Schema\”.
Important note: If you are running BizTalk Server 2006, you first have to get an updated version of the Msgbox_cleanup_logic.sql file to (re)create the bts_CleanupMsgbox stored procedure at http://support.microsoft.com/kb/924715. The hotfix is available for download directly without having to contact MS. If you are running BizTalk Server 2006 R2 then the updated version of the Msgbox_cleanup_logic.sql file is already installed and you do not need to download the hotfix. - Execute the stored procedure bts_PurgeSubscriptions on your message box database. If the stored procedure does not exist, you can create it by inspecting the sql script “msgboxlogic.sql” found in the directory “\Schema\”. Copy paste only the part of the script that creates this specific stored procedure and run it.
As deleting data in a database does not reduce the size the database files on the disk; you need to shrink the database files if you want to reduce its physical size. There are 2 simple ways to do it:
- Through SQL Server Management Studio, right click on the BizTalkMsgDb database, click on Tasks > Shrink > Database
- Through T-SQL, using the DBCC SHRINKDATABASE command: DBCC SHRINKDATABASE (BizTalkDTADb);
Microsoft does not support this procedure on production systems simply because this will purge production data. So, as long as you keep in mind that all existing messages will be purged, it is safe to run it on a production environment.
Truncating the Database Log File:
There is also another useful command to truncate the database logfile, would it be necessary (such as running out of disk space):
BACKUP LOG BizTalkDTADb WITH TRUNCATE_ONLY (*)
Note that you are not supposed to do this on production but emergency scenarios can call for the necessity.
(*) Edit:
The T-SQL BACKUP command does not support the TRUNCATE_ONLY parameter since SQL Server 2008. See T-SQL BACKUP Reference.
As explained in this post, it was discontinued because it breaks the ability to recover from a certain point in time until a full backup is run again. It was intended to be used only in case of emergency such as when we run out of disk space but apparently it was regularly misused by DBAs and maybe the reason was that it was a misunderstood option of the BACKUP command. It can indeed be a little misleading that an option of the BACKUP command can “break” the backup.
The way to truncate the log file on SQL Server 2008 and above in case of emergency is the following:
- Set the Database in Simple Recovery mode.
- Shrink the log file.
- Set the Database back into Full recovery mode.
Example taken from DBCC SHRINKFILE T-SQL reference:
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO