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 sethu.srinivasan@microsoft.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:
connect.microsoft.com/.../ViewFeedback.aspx
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
Thanks
Sethu Srinivasan
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 !!
Hi,
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.
Salil
Thank you.
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