Microsoft SQL Maintenance Plans – Part One

I’m collecting a series of notes on MSSQL maintenance plans.  There may be some bits herein that can assist; please comment liberally and provide pointers if you think I’ve left something out.

My primary contact with these plans is through BizTalk Server 2004.  More detail on these jobs is available in the BizTalk Database Appendix (not yet published in this BLOG), my reference posts and in the BizTalk documentation on MSDN.

I mention these specifically because 1) I work with them the most and 2) I recently resolved issues with these jobs on a system;  my memory is still fresh.  Some maintenance notes:

  • When creating a maintenance plan, group databases with transactional logging (full recovery mode) together. Mixing with other transaction types (simple) will generate errors.  You’ll get ‘(SQLSTATE 42000)’ and ‘(ERROR 22029)’ error messages in the job history and error number 208 in the Windows Event Log.  Look at each database and ensure the modes are the same for the databases selected for this operation.
  • The above error will also arise if you attempt an operation that requires the database to be in single-user mode (rebuilding an index, for example).  To look for this issue, click “Management“ and right-click “Database Maintenance Plans” , selecting “Maintenance Plan History“.  Sort for the failed job and click “Properties”.   Troubleshooting Database Maintenance Plan Failures on MSDN provides some hints on the above issues.
  • If you see the ever-present (for me, anyway) “The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060)“ error, on a BizTalk server the cause is a missing adm_OtherBackupDatabases table in the BizTalkMgmtDb database.  Create this table per the Microsoft SQL Maintenance Jobs for BizTalk Server article on this blog.

More to follow.

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: Logo

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

Facebook photo

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

Connecting to %s

%d bloggers like this: