BizTalk Server 2010: SQL Agent Jobs

BizTalk Server 2010: SQL Agent Jobs



BizTalk Server SQL Agent Jobs

BizTalk is shipped out with a total of 13 SQL Agent jobs. Two of these jobs must be configured. The two jobs that needs configuration are the two most important jobs. The "Backup BizTalk Server" and the "DTA Purge and Archive". For more information about the different jobs look at the headings below.

Backup BizTalk Server

This is the job provided by Microsoft to do a best practice backup of the BizTalk databases. This job has to be configured for it to be able to run. Look at the references on how to set up this job. The job contains three steps

  • Step 1: Turns on/off data compression
  • Step 2: Performs a full backup of the database
  • Step 2: Performs a full backup of the log tables
  • Step 3: Performs a cleanup of the backup history according for how long a backup should be kept

References

Configuring the Backup BizTalk Server job

CleanupBTFExpiredEntriesJob_BizTalkMgmtDb

Criticality : Low

This job will delete all expired BizTalk Framework from the management database, it automates the archiving of tracked messages and purging of the BizTalk tracking database. This is to maintain a healthy system and to keep the tracking data archived for future use.

MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb

Criticality : High

This SQL Agent job detects whenever a Host Instance has stopped responding and releases all the work for this host instance in the message box so that another Host Instance can take over the job.

MessageBox_Message_Cleanup_BizTalkMsgBoxDb

Criticality : Very High

This SQL Agent jobs removes and message that are no longer referenced by any subscribers in the message box database, it also initiates the SQL Agent job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb.

MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb

Criticality : Very High

This SQL Agent Job manages the reference count logs for messages and determine when a message in the Message box database is no longer being referenced to a subscriber.

MessageBox_Parts_Cleanup_BizTalkMsgBoxDb

Criticality : Medium

This SQL Agent job removes all message parts in the message box that are no longer referenced by and messages or instances.

MessageBox_UpdateStats_BizTalkMsgBoxDb

Criticality : Medium

This SQL Agent Job updates statistics for the message box database.

Monitor BizTalk Server

This SQL Agent Job is new to BizTalk 2010, it scans for any errors on the BizTalk servers, for example orphaned instances, however it does not solve any issues.
It searches the BizTalk databases to locate any issues described in the bulletin below:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled

If this jobs encounters and errors it will fail and report the error in the SQL Agent log file 

Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb

Criticality : Medium

This SQL Agent job is used if you have multiple messages box databases. it asynchronously performs operational actions to convey the information to the correct database. 

PurgeSubscriptionsJob_BizTalkMsgBoxDb

Criticality : Medium

This SQL Agent job purges unused subscription predicates from the Message box database.

Rules_Database_Cleanup_BizTalkRuleEngineDb

Criticality : Low

This SQL Agent job purges old audit data from the Rule Engine database that is over 90 days, it also purges old history data (deploy/undeploy notifications from the rule engine database every third day.

TrackedMessages_Copy_BizTalkMsgBoxDb

Criticality : Medium

This SQL Agent job copies message bodies from a tracked messages into the Tracking database from the message box database.

DTA Purge and Archive

This SQL Agent job purges and archives information from the tracking database, you do need to configure this job in order for it to work.

Reference for DTA Purge And Archive

Configure the DTA Purge and Archive SQL Agent job

Reference

Information regarding the SQL Agent jobs for BizTalk

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Maheshkumar S Tiwari edited Revision 16. Comment: minor edit

  • Lex Hegt edited Revision 14. Comment: Fixed a typo

  • Lex Hegt edited Revision 13. Comment: Added Step 1 which turns on/off data compression

  • Steef-Jan Wiggers edited Revision 12. Comment: Removed duplicate link

  • Steef-Jan Wiggers edited Revision 11. Comment: Minor edit

  • Sandro Pereira edited Revision 7. Comment: Fix the number of jobs from 12 to 13

  • Richard Mueller edited Revision 4. Comment: Clean up headings (for consistency), fix duplicate <a name> tags in HTML

  • shankarmaha edited Revision 3. Comment: Added criticality section for the jobs

  • Ed Price - MSFT edited Revision 1. Comment: Title casing and formatting/indenting

  • Tord G.Nordahl edited Original. Comment: added reference, spelling

Page 1 of 1 (10 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Tord G.Nordahl edited Original. Comment: added reference, spelling

  • Ed Price - MSFT edited Revision 1. Comment: Title casing and formatting/indenting

  • shankarmaha edited Revision 3. Comment: Added criticality section for the jobs

  • Richard Mueller edited Revision 4. Comment: Clean up headings (for consistency), fix duplicate <a name> tags in HTML

  • Sandro Pereira edited Revision 7. Comment: Fix the number of jobs from 12 to 13

  • Steef-Jan Wiggers edited Revision 11. Comment: Minor edit

  • Steef-Jan Wiggers edited Revision 12. Comment: Removed duplicate link

  • Lex Hegt edited Revision 13. Comment: Added Step 1 which turns on/off data compression

  • Lex Hegt edited Revision 14. Comment: Fixed a typo

  • Great piece of work!

  • Maheshkumar S Tiwari edited Revision 16. Comment: minor edit

Page 1 of 1 (11 items)