If you are using SQL 2012, please take a look at http://blogs.msdn.com/b/sqlagent/archive/2012/04/05/remove-associated-data-collector-jobs-in-sql-2012.aspx
**NOTE**Please use the following script only for testing purpose on TEST systems.
T-SQL script in this blog entry can be used to remove the associated data collector objects that were created while enabling data collector. http://connect.microsoft.com/SQLServer/feedback/details/334180/data-collector-remove-data-collector-to-remove-associated-objects
This script deletes SQL agent jobs that were created while enabling data collector and also restores the state of data collector as it was before data collector was enabled. we would like to hear get your feedback on this proposed solution. email me at email@example.com
You can also download copy of the script from dccleanup.sql
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
In fact, I tried the script supplied here before I tried yours:
After I cleaned the MDW again with your script I could re-install it, but the collector jobs will not start due to this error:
Unable to start collection set Disk Usage.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Caught error#: 14373, Level: 16, State: 1, in Procedure: sp_verify_schedule_identifiers, Line: 29, with Message: Supply either @schedule_id or @schedule_name to identify the schedule. (Microsoft SQL Server, Error: 14684)
Please try the solution provided in blog entry on a clean SQL Server installation that has data collector configured. Other workarounds provided in connect.microsoft.com/.../ViewFeedback.aspx may have deleted related schedules while dropping data collector jobs
SQL Server [MSFT]
Thank you Very much !! I was trying to create a UTILITY Control Point but it was failing due to already running data collection. Even after disabling teh feature teh vaildation will not pass for UCP creation. Then Google brings me here to your blog and Yupeeeeeeee i executde the script and passed the validation!!
Thank you very much to put this script here. You Rock !!
I have MDW configured on one of my dev instances. Data collection is happening but graphical reports are currently not getting generated for server activity and query analysis. Let me know any workarounds for that or should i just cleanup the MDW using the above script and start the configuration from scratch. Reason why i wanted to start from scratch is because the instance on which its running is recently upgrade to SQL Server 2008R2 from SQL Server 2008.
Worked fine on our R2 test server.
sp_syscollector_cleanup_collector stored procesure is available in msdb database in SQL 2012 RC0 www.microsoft.com/.../future-editions.aspx
I ran this script which did a nice job of cleaning up the jobs. However I'm left with the data collection database which I can't drop. I tried running a script to kill all users in the database and yet I still get the same error:
Msg 3702, Level 16, State 4, Line 4
Cannot drop database "PerformanceDW" because it is currently in use.
ALTER DATABASE [PerformanceDW]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [PerformanceDW]
To drop the database, right click on the database icon in the object explorer tree and choose "delete". In the drop database dialog select "kill all open connections" at the bottom. This will sever all open connections and force the drop.
I still have this job on my server even after running this script.mdw_purge_data_[PerformanceDW]. I'm wondering if I can delete it but from what I'm reading some people have corrupted the msdb doing this. I figured it would be prudent to ask before I do it. None the less I guess the safest thing to do is back it up and then try it.
Works perfectly, thanks heaps!
Thanks a lot. Script worked on SQL 2008 r2.