how to remove utility control point jobs and tables?

Question

I set up a UCP and decided to remove it after a few days. It was easy to remove the instances in SSMS and then EXEC msdb.dbo.sp_sysutility_ucp_remove.

However, 3 SQLAgent jobs that were created on every SQL instance are still there and I can't remove them. The Drop fails because of Foreign Keys on tables that were also created by the UCP setup.

I would like to remove all those tables and jobs (and other objects) taht weren't there before the UCP was set up. How can I do that?

(Windows Server 2008 R2 + SQL Server 2008 R2)

Klaas

Answer 1

Hi KlassV1,

Before you run the procedure sp_sysutility_ucp_remove, note the following requirements:

This procedure must be run on a computer that is a UCP.
This procedure must be run by a user with sysadmin permissions, the same permissions required to create a UCP.
All managed instances of SQL Server must be removed from the UCP. How to remove an instance of SQL Server from the SQL Server Utility: http://msdn.microsoft.com/en-us/library/ee210565(SQL.105).aspx.

Then run the procedure sp_sysutility_ucp_remove. Make sure that you have followed the steps. I think when you remove the instances the SQL Server jobs would be removed along with. Or you could manually remove the SQL Agent jobs.

If it is not work, please post the error messages in detail.

 

Thanks,
Maggie

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

Hi Maggie

I did exactly what you describe. Remove instances worked fine, and the sp_sysutility_ucp_remove also. All reported "succeeded".

There are 3 jobs: 'collection_set_5_noncached_collect_and_upload', 'sysutility_mi_collect_and_upload', 'sysutility_mi_collect_performance' which are disabled by one of the remove procedures, but not deleted. So I want to delete manually in SSMS, both 'sysutility...' jobs are gone, but the 'collection...' job not and the response is

"Drop failed.... The DELETE statement conflicted with the REFERENCE constraint 'FK_syscollector_collection_sets_collection_sysjobs'. The conflict occurred in database "msdb", table "dbo.syscollector_collection_sets_internal", column 'collection_job_id'. The statement has been terminated; (Microsoft SQL Server, Error: 547)

This happens on all ex-instances and the ex-UCP.

Thank you for your answer,

Klaas

Answer 3

collection_set_5_noncached_collect_and_upload

Hi, I have same problem.  I cant delete jobs

collection_set_5_noncached_collect_and_upload.  I removed from the UCP all instances.

"Drop failed.... The DELETE statement conflicted with the REFERENCE constraint 'FK_syscollector_collection_sets_collection_sysjobs'. The conflict occurred in database "msdb", table "dbo.syscollector_collection_sets_internal", column 'collection_job_id'. The statement has been terminated; (Microsoft SQL Server, Error: 547)

Anthony

---

Microsoft Influencer

Answer 4

You can follow the steps listed in

Remove associated data collector jobs in SQL 2012

http://blogs.msdn.com/b/sqlagent/archive/2012/04/05/remove-associated-data-collector-jobs-in-sql-2012.aspx

SQL 2008, SQL 2008 R2: http://blogs.msdn.com/b/sqlagent/archive/2011/07/22/remove-associated-data-collector-jobs.aspx

Thanks

Sethu  Srinivasan  [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent