How to purge BizTalkMsgDb?

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 “<BizTalk installation 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 “<BizTalk installation 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);

There is also another useful command to truncate the database logfile, would it be necessary:
BACKUP LOG BizTalkDTADb WITH TRUNCATE_ONLY

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.

Turn off tracking globally in BizTalk Server 2006

The BizTalk Tracking (BizTalkDTADb) database grows in size as BizTalk Server processes data on your system. If the size of the BizTalk Tracking database causes poor disk performance or fills up the disk subsystem, you can manually purge the data from the Tracking database. See my previous post about how to purge and maintain the BizTalkDTADb database.
If you repeatedly have issues with the BizTalk tracking database, you may want to configure BizTalk to no longer collect tracking information. This is possible by turning off global tracking for the whole BizTalk Server.

Here is the procedure to turn off tracking globally for BizTalk server 2006 and 2006 R2:

• Open SQL Server Management Studio and connect to the Database Server where the BizTalk Management Database is running, BizTalkMgmtDb
• Expand the BizTalkMgmtDb, database, expend Tables, right-click the adm_Group table, and then click Open Table.
• In the GlobalTrackingOption column, change the value from 1 to 0 and then press ENTER. A value of 0 turns off global tracking for the whole BizTalk server while a value of 1 turns it on.
• Restart all your BizTalk hosts for the change to take effect.

As when Tracking is turned off, tracking information is not collected so no information will be available in HAT anymore (Health and Activity Tracking). You should consider this effect against keeping tracking data for a shorter time when thinking about turning off Tracking globally.

An alternative to turn off Tracking globally is to turn it off on an application per application basis.
I think that once a BizTalk application is deployed and running smoothly for a while, there is no reason to still have tracking turned on at all time.
If you deploy new BizTalk applications or keep updating existing ones on your production server regularly, you will probably want to be able to consult HAT after the application is freshly deployed. For that to be possible you would have to keep global tracking on.
In that case, I think that a best practice kind of approach is to have Tracking turned on for the freshly deployed application for a few days until you deem it running fine so that you do not need to consult HAT anymore. Once HAT is not needed regularly anymore, you can use the BizTalk Server Administration Console to disable tracking for all the artifacts belonging to the concerned application (orchestrations, ports, etc.). Would you need to turn it on again, it would just take a few minutes to configure tracking back on for the application's artifacts.

Reference can be found at the MSDN BizTalk documentation How to Turn Off Global Tracking

BizTalkDTADb grows too large – How to purge and maintain the database?

The BizTalkDTADb is a BizTalk database that stores health monitoring data tracked by the BizTalk Server tracking engine. It is commonly called the “BizTalk Tracking Database”. This database can grow relatively quickly in size depending of the kind of load your server is under.

I will explain first what should be done to keep the database healthy (by which I mean to keep it under a reasonable size) and after how to clean up the database if it grew up so large that the normal clean up method doesn't work anymore.

1. How to maintain the BizTalkDTADb?

Each BizTalk service instance running is processing data and while the data is processed, BizTalk tracks it and saves it in the BiztTalk Tracking Database. This means that the Tracking Database will grow indefinitely over time which is obviously not a viable option.
There is an SQL job called “DTA Purge and Archive (BizTalkDTADb)” that is installed on the BizTalk SQL Server which is used for cleaning up (deleting old tracking information) the BizTalkDTADb. That job is not enabled by default so the first thing that should be done after installing BizTalk server is to configure and enable the job. See here for information about how the cleanup process works and here for information on how to configure the SQL job. Basically, the job calls a single stored procedure on the BizTalkDTADb and once edited should looks like the following:

exec [dbo].[dtasp_BackupAndPurgeTrackingDatabase] 1, 0, 1, '\\MyBizTalkServer\backup', null, 0

The 4 first parameters are the one that you need to know about. The 2 first are the number of hours and days for which completed instances will be cleaned up. The third one is the number of days after which even non completed instance will be cleaned up. The fourth is the location of the backup folder.
This means that the SQL job will back up the BizTalkDTADb each time it runs, making that backup files will fill up your disk subsystem pretty quickly if nothing is done about it! Backups are important in case of a Database crash and that the Tracking Database needs to be restored.

If you do not consider the Tracking Database to be of enough importance to be backed up and have the extra burden to manage the backups, you can modify the “DTA Purge and Archive (BizTalkDTADb)” SQL job as explained here. This way, the job will only purge the tracking database without backing it up. It is especially applicable for development and QA environments and might also apply to your production environment.
In short, the only change that needs to be done in the SQL job is to modify the T-SQL statement run by the job. It needs to execute the SP dtasp_PurgeTrackingDatabase instead of dtasp_BackupAndPurgeTrackingDatabase.

The final T-SQL statement executed by the SQL job will be similar to the following:

declare @now as datetime
set @now = GetUTCDate()
exec [dbo].[dtasp_PurgeTrackingDatabase] 0, 3, 6, @now

In this case I keep complete instances in the Tracking DB for 3 days and incomplete one for 6 days, everything older should be purged. As you see there is no path to specify for the backup location as no database backup is executed.

Instead of modifying the original SQL job you could alternatively disable it and create a new job with the appropriate T-SQL call. That is how I have done it myself and consider it to be a best practice.
Moreover, I scheduled the job to run every 5 minutes. This has proven to be a good time interval. I used to run the job every 30 minutes only but I ever encountered cases where the clean up procedure did not keep up with the amount of tracked data and I ended up with a huge tracking database which I had to purge manually, as I will explain next.
So, a 5 minutes interval to run the job seems to also be a best practice from my experience.


2. How to manually purge the BizTalkDTADb?

You will have to manually purge the BizTalkDTADb database if it grew too large either because the clean up procedure was not started or the clean up procedure could not keep up with the amount of data saved in the Tracking Database.
This is explained in details here but, in short, the important points are:

- All the BizTalk services used by BizTalk needs to be stopped. This means all the BizTalk host service instances, Enterprise SSO, BizTalk Rules Engine, EDI service, BAM, BAS and IIS if they are used.

- Open Microsoft SQL Server Management Studio and run the following SQL statement on the BizTalkDTADb: exec dtasp_PurgeAllCompletedTrackingData

Once the procedure is executed, a lot of space will have been freed in the Tracking Database. The database will nevertheless still take the same amount of space on the disk subsystem because deleting data in a database does not reduce the size the database takes on the disk. If you want to reduce its size on the disk, you need to shrink it. You can do that in 2 ways:

1. Through SQL Server Management Studio, right click on the BizTalkDTADb database, click on Tasks > Shrink > Database



2. Through T-SQL using the DBCC SHRINKDATABASE command:
DBCC SHRINKDATABASE (BizTalkDTADb);
The reference of the T-SQL DBCC SHRINKDATABASE command can be found here.