Geek with special skills

BIZTALK: Database Structure and Jobs

The last week I have implemented a new Biztalk environment with the databases on a SQL 2008 R2 SP1 failover cluster. I’ve found some interesting information about Biztalk design, functionality and tuning that i would like to share with you.

This topic shows the database structure and database jobs for BizTalk Server 2006.

The following figure shows the processes and entities that write to the BizTalk Server databases.

Database write diagram showing the processes and entities that write to the BizTalk Server databases
Processes that write to BizTalk Server databases

BizTalk Server 2006 includes the following SQL Server Agent jobs to assist you in managing the BizTalk Server databases:

The names of the jobs change depending on the database names given during configuration. If you have deployed multiple MessageBox databases in your environment, there will be several jobs for each MessageBox.
In the BizTalk Management (BizTalkMgmtDb) database, there’s a stored procedure named dbo.adm_cleanupmgmtdb. DO NOT RUN THIS STORED PROCEDURE! If you do run this stored procedure, all the entries in the database will be deleted.
Job Description
Backup BizTalk Server (BizTalkMgmtDb) This job performs full database and log backups of the BizTalk Server databases. For more information about configuring and running this job, see Backing Up and Restoring BizTalk Server Databases.
CleanupBTFExpiredEntriesJob_BizTalkMgmtDb This job cleans up expired BizTalk Framework (BTF) entries in the BizTalk Management (BizTalkMgmtDb) database.
DTA Purge and Archive (BizTalkDTADb) This job automatically archives data in the BizTalk Tracking (BizTalkDTADb) database and purges obsolete data. For more information about configuring and running this job, see Archiving and Purging the BizTalk Tracking Database.
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb This job detects when a BizTalk Server host instance (NT service) has stopped and releases all work that was being done by that host instance so that it can be worked on by another host instance.
MessageBox_Message_Cleanup_BizTalkMsgBoxDb This job removes all messages that are no longer being referenced by any subscribers in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables.

This is an unscheduled job which is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Do not manually start this job.
MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb This job manages the reference count logs for messages and determines when a message is no longer referenced by any subscriber.

Even thought this SQL Server Agent job is scheduled to run once per minute, the stored procedure that is called by this job contains logic to ensure that the stored procedure runs continually. This is by design behavior and should not be modified.
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb This job removes all message parts that are no longer being referenced by any messages in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables. All messages are made up of one or more message parts, which contain the actual message data.
MessageBox_UpdateStats_BizTalkMsgBoxDb This job manually updates the statistics for the BizTalk MessageBox (BizTalkMsgBoxDb) database.
Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb This job is needed for multiple MessageBox deployments. It asynchronously performs operational actions such as bulk terminate on the master MessageBox after those changes have been applied to the subordinate MessageBox.
PurgeSubscriptionsJob_BizTalkMsgBoxDb This job purges unused subscription predicates from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database.
Rules_Database_Cleanup_BizTalkRuleEngineDb This job automatically purges old audit data from the Rule Engine (BizTalkRuleEngineDb) database every 90 days. This job also purges old history data (deploy/undeploy notifications) from the Rule Engine (BizTalkRuleEngineDb) database every 3 days.
TrackedMessages_Copy_BizTalkMsgBoxDb This job copies the messages bodies of tracked messages from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database to the Tracking (BizTalkDTADb) database.

03/08/2012 - Posted by | Biztalk, Sql Server | ,


  1. Its like you read my mind! You appear to know so much about this, like you wrote
    the book in it or something. I think that you could do with a few
    pics to drive the message home a little bit, but instead of that, this is excellent blog.
    A fantastic read. I’ll definitely be back.

    Comment by Fixed Junk Cars | 05/17/2013 | Reply

    • Thanks for your feedback. Im glad you liked this post.

      Kind Regards

      Comment by gaptheguru | 06/05/2013 | Reply

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: