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

Posted: November 18, 2014 in BizTalk
Tags: , , , , , ,

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 the other jobs should not be disabled!

However one of the most common and important tools to resolve problems that happens 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.

disable-BizTalk-SQL-Server-Agent-jobs

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 the task 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'

The SQL queries can be found and download on Microsoft TechNet Gallery:
SQL Query to Disable All BizTalk SQL Server Agent jobs
Microsoft TechNet Gallery

 

SQL Query to Enable All BizTalk SQL Server Agent jobs
Microsoft TechNet Gallery

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s