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
by: mSharfiPosted on 2009-09-19 at 07:47:46ID: 25373200
Hi
I hope you fine, please try to use this T-SQL and check the result:
dbcc checkdb (dbname) with no_infomsgs
thanks