BizTalk is shipped out with a total of 13 SQL Agent jobs. Experienced BizTalk professionals know that all the BizTalk SQL Server Agent jobs except the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job should be enabled and running successfully. Besides this job, all the other jobs should not be disabled!
However, one of the most common and important tools to resolve problems that happen in BizTalk Server is the BizTalk Terminator tool (you can know more about this tool here). Terminator provides the easiest way to resolve most of these issues identified by the Monitor BizTalk Server Job.
Nevertheless, before running the Terminator tool, you must always make sure that:
- you have a BizTalk Backup of your databases
- all the BizTalk hosts instances have been stopped,
- and all BizTalk SQL Agent jobs have been disabled.
Unfortunately, through SQL Server Management Studio console there is no easy way to disable or enable all jobs, forcing us to go one on one to disable or enable them.
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 SQL Query to Enable All BizTalk SQL Server Agent jobs from GitHub here:
You can download SQL Query to Disable All BizTalk SQL Server Agent jobs from GitHub here: