Link to home
Start Free TrialLog in
Avatar of 243johnm
243johnm

asked on

Cannot Add or Delete Corrupt Maintenance Plans

I setup some Maintenance Plans in SQL Server 2005 Workgroup for a Maximizer CRM client last year. When working on their server recently, I noticed that the transaction logs were no longer truncating after backups. Furter investigation revealed that the two mainteance plans appeared to be blank.

When I tried to modify one of the maintenance plans in Management Studio, I received the following error message:

Microsoft SQL Server Management Studio
Value cannot be null
Parameter name: component (System.Desgn)
Microsoft SQL Server Management Studio Value cannot be null Parameter name: component (System.Desgn)



In the Error List, I found the following:
Microsoft SQL Server Management Studio is unable to load this document:
Error      1      Error loading 'System Database Maintenance Plan - sa' : Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSApplication90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{7591CC39-276A-4973-97C7-4EFEA5A5788F}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).. server=SERVEUR;package=Maintenance Plans\System Database Maintenance Plan;username=sa;      1      1      



Despite that, I found this in the Event Viewer:
SQL Server Scheduled Job 'System Database Maintenance Plan.Subplan_1' (0x39B25A98DD7D55428F9F2B6B16BECF3A) - Status: Failed - Invoked on: 2009-09-18 13:55:00 - Message: The job failed.  The Job was invoked by Schedule 10 (System Database Maintenance Plan).  The last step to run was step 1 (Subplan_1).



When trying to delete the defective maintenance plans, I get this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

------------------------------

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)



Advanced Information shows this:

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
Program Location:

   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropMaintPlans.DoDropMaintPlan90(String planID, String planName)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropMaintPlans.DoDropObject(Int32 objectRowIndex)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)

===================================

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.InitFromExistingPackage(Package package, ServerConnection localConnObj)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan..ctor(Package package, String serverName, String userName, SqlSecureString pwd)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.GetMaintenancePlanFromServer(String maintPlanName, String serverName, String userName, SqlSecureString pwd)

===================================

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)



When trying to add a new maintenance plan, I get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)



Advanced Information provides this:

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
Program Location:

   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
   at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()

===================================

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Package..ctor()



Here are the SQL Server application details:
Microsoft SQL Server Management Studio: 9.00.4035.00
Microsoft Analysis Services Client Tools: 2005.090.4035.00
Microsoft Data Access Components (MDAC): 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML: 2.6 3.0 4.0 6.0
Microsoft Internet Explorer: 8.0.6001.18702
Microsoft .NET Framework: 2.0.50727.3082
Operating System: 5.2.3790

Any ideas on how to delete the corrupt maintenance plans and create new ones? SQL Server is working just fine...

Thanks very much,

John
Avatar of Mohed Sharfi
Mohed Sharfi
Flag of Sudan image

Hi
I hope you fine, please try to use this T-SQL and check the result:
dbcc checkdb (dbname) with no_infomsgs
thanks
Avatar of Raja Jegan R
All these Maintenance plans will be stored as a Package in Integration Services Instance.
Just login to your SSIS Instance service and expand Stored Packages option..

You can see all  your Maintenance Plans over there.
Just Delete your Corrupted Maintenance Plans and recreate it
also try to install SP2.and make sure  the Integration services. SSIS is running properly
mSharfi,
     243johnm is already at SP3..
Avatar of 243johnm
243johnm

ASKER

Thanks very much mSharfi & rrjegan17. I have some more questions for you...

mSharfi: I thought "dbcc checkdb (dbname)" was to check for corruption in a database. Does it also check Maintenance Plans?

rrjegan17: you say to "Just login to your SSIS Instance service and expand Stored Packages option.." I checked and I didn't see SSIS in the under Microsoft SQL Server 2005 in Start > Programs. It also wasn't on a menu in Management Studio. Is it an optional install component or can I start it from the command line?

Regards,

John
I never rec'd an answer to this question:

rrjegan17: you say to "Just login to your SSIS Instance service and expand Stored Packages option.." I checked and I didn't see SSIS in the under Microsoft SQL Server 2005 in Start > Programs. It also wasn't on a menu in Management Studio. Is it an optional install component or can I start it from the command line?

If rrjegan17 can't answer it, can anyone else?

Thanks very much,

John
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much rrjegan17!  Re "Have been bit busy over here" - not a problem at all, work comes before Exp-Exch!

Looks like SSIS isn't installed: I get an error message: Connect to SSIS Service on machine "Machine-Name" failed: The specified service does not exist as an installed service.

I'm going to start a new thread on how to get that component installed...

Thanks again,

John
Welcome..

>> I'm going to start a new thread on how to get that component installed...

Just Click Change / Remove SQL Server 2005 on Add/ Remove Programs in Control Panel and then include Integration Services as part of your Installation.

It would ask for your Installation DVD and providing so will get you Integration Services installed.