SQL Queries to Enable or Disable All BizTalk SQL Server Agent jobs

BizTalk Server installs a total of 13 SQL Server Agent jobs.
Experienced BizTalk administrators know that almost all of these jobs must remain enabled and run successfully. The only exception is the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job.

In other words, apart from that job, you should not disable the remaining BizTalk SQL Agent jobs during normal operation.

Why Disable the Jobs?

One of the most important tools used to resolve serious BizTalk Server problems is the BizTalk Terminator tool. Terminator provides the easiest way to fix many issues detected by the Monitor BizTalk Server Job.

However, before running the Terminator tool, you must always ensure that:

• You created a full BizTalk database backup.
• All BizTalk host instances are stopped.
• All BizTalk SQL Server Agent jobs are disabled.

Unfortunately, SQL Server Management Studio does not provide a quick way to enable or disable all jobs at once. Therefore, administrators must normally disable them one by one, which is slow and error-prone.

This query will disable all the BizTalk SQL Server Agent jobs (including the MessageBox_Message_Cleanup_BizTalkMsgBoxDb):

update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'Backup BizTalk Server (BizTalkMgmtDb)'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'DTA Purge and Archive (BizTalkDTADb)'
...
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'TrackedMessages_Copy_BizTalkMsgBoxDb'
update msdb.dbo.sysjobs set [enabled] = 0 where [name] = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb'

After all these tasks are complete, we need, of course, to enable and start everything. This query will enable all the BizTalk SQL Server Agent jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb):

update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'Backup BizTalk Server (BizTalkMgmtDb)'
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb'
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'DTA Purge and Archive (BizTalkDTADb)'
...
update msdb.dbo.sysjobs set [enabled] = 1 where [name] = 'TrackedMessages_Copy_BizTalkMsgBoxDb'

Download

THESE SQL SCRIPTS ARE PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download the SQL Query to Enable All BizTalk SQL Server Agent jobs from GitHub here:

You can download the SQL Query to Disable All BizTalk SQL Server Agent jobs from GitHub here:

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira