SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Maintenance Plan Issues

I think maintenance plans were a great improvement in SQL Server 2005, with a more visual designer and the ability of the DBA to chain together whatever tasks they wanted.  Service Pack 2 brought some enhancements, and a few bugs, so I’d recommend that you apply SP3 to your servers and clients if you are using them.

I rarely see issues with building packages since it’s a fairly simple designer, but there are two places where I have seen people have trouble editing them.

  1. Version differences
  2. Outside editors

I’ll explain the basic issues I’ve seen to give you a few things to check if you can’t open a plan.

Version Differences - The designer for maintenance plans is a cut-down version of the SSIS editor. As a result, there are sometime changes or enhancements to this designer delivered in SPs or CUs. If you have created a package in one version of SSMS, often you cannot open that package in a lower version. So the first thing to check is that the server and/or designer client is at a higher version than the one that is failing.

Outside Editors – The designer is a version of the SSIS designer, which is a version of Visual Studio. However those products contain many more features, and while you can use them to edit your maintenance plans, you can introduce changes to the plan that the basic SSMS designer does not support. As a result, you can’t then open the package again in the SSMS designer if you’ve opened it in Visual Studio or BIDS and changed something.

There usually are error messages to clue you in to the issue, but these are the two things I’d check first if you have issues modifying maintenance plans in SSMS.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.