Microsoft SQL Maintenance Jobs for BizTalk Server 2004

This article covers some observations and challenges with the MSSQL maintenance jobs installed with BizTalk Server 2004.  For error messages I’ve seen associated with other MSSQL maintenance jobs, please see my blog post: Microsoft SQL Maintenance Jobs for BizTalk Server 2004.  I will update this article as I continue to resolve issues and receive feedback.

To refresh; BizTalk Server 2004 creates 8 maintenance tasks to keep the MessageBox and other BizTalk databases running smoothly.  The jobs include:

  • Backup BizTalk Server
  • CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
  • MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
  • PurgeSubscriptionsJob_BizTalkMsgBoxDb
  • TrackedMessages_Copy_BizTalkMsgBoxDb
  • TrackingSpool_Cleanup_BizTalkMsgBoxDb

More detail on these jobs is available in the BizTalk Database Appendix (in progress; contact me for an advance copy) and in the BizTalk documentation on MSDN.  These jobs are set up to run at default intervals ranging from once a minute to once a week and, for the most part, require little tweaking.

You must edit and enable the first and last jobs on the list in order for them to function.  For more detail, see the BizTalk Database Appendix (in progress; contact me for an advance copy).  If you see the jobs failing, view the job history; some interesting error messages can arise:

The following error occurs if you don’t install all the features of BizTalk Server 2004 (HWS, BRE, etc.):

“Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Message 1708)  Invalid object name ‘adm_OtherBackupDatabases’. [SQLSTATE 42S02] (Error 208)  Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Error 1708).  The step failed.“

To resolve this issue, create the ‘adm_OtherBackupDatabases’ table in the BizTalkMgmtDb (or it’s equivalent in your installation) in SQL Query Analyzer with the following script:

CREATE TABLE [dbo].[adm_OtherBackupDatabases] (
[DefaultDatabaseName] [nvarchar] (128) NOT NULL ,
[DatabaseName] [nvarchar] (128) NOT NULL ,
[ServerName] [nvarchar] (80) NOT NULL ,
[BTSServerName] [nvarchar] (80) NOT NULL
)
GO

For more detail, please see “The Backup BizTalk Server SQL Job Fails ..“ article in MSDN.

Jeff Lynch has a useful article on these jobs in BizTalk Server 2004 – Database Growth.  It’s well worth a look.

Advertisements

About Michael Coates
I am a pragmatic evangelist. The products, services and solutions I write about fulfill real-world expectations and use cases. I stay up-to-date on real products I use and review, and share my thoughts here. I apply the same lens when designing an architecture, product or when writing papers. I am always looking for ways that technology can create or enhance a business opportunity .. not just technology for technology's sake. My CV says: Seasoned technology executive, leveraging years of experience with enterprise and integration architectural patterns, executed with healthy doses of business acumen and pragmatism. That's me. My web site says: Technology innovations provide a myriad of opportunities for businesses. That said, having the "latest and greatest" for its own sake isn't always a recipe for success. Business successes gained through exploiting innovation relies on analysis of how the new features will enhance your business followed by effective implementation. Goals vary far and wide: streamlining operations, improving customer experience, extending brand, and many more. In all cases, you must identify and collect the metrics you can apply to measure your success. Analysis must be holistic and balanced: business and operational needs must be considered when capitalizing on a new technology asset or opportunity.

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

%d bloggers like this: