Skip to content

Archive for

SQL Server Maintenance plan not opening

You may get below error when you try to open any SQL Server Maintenance Plan: –

TITLE: Microsoft SQL Server Management Studio
‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online. (ObjectExplorer)

Issue: –
Its possible that some one have disabled Agent XPs component on your server.

Resolution: –
Run below sql query to re-enable Agent XPs and then try to open maintenance plan again. It will work.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Not able to delete SQL Server Maintenance Plan and/or SQL Server Agent jobs

You may get below error while trying to delete MP after server name change: –

TITLE: Microsoft SQL Server Management Studio

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

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package “Maintenance Plans\UserDB_Tlog” from SQL Server. 

and/or

You may get below error while trying to delete SQL Server agent job: –

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job ‘UserDB_Tlog.Subplan_3’.  (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.

The statement has been terminated.

The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4060&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

Possible cause of this issue: –

Mainly you will face issue if your SQL Server host name is changed.

Solution: –

In order to delete the maintenance plan and/or SQL Server agents jobs, we have to manually delete the entries from system tables in msdb database. Below are the queries you need to fire in msdb database in order to get this done.

select * from sysjobs -- to find the correct jobid

select * from sysjobschedules where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysjobschedules] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select plan_id from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select * from dbo.sysmaintplan_log where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value from above sql

delete from [dbo].[sysmaintplan_log] where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value frm above sql

select * from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from sysjobs where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

Now connect to integration services on this server and manually delete the package under msdb