Remove associated data collector jobs

If you are using SQL 2012, please take a look at

**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.

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

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


Comments (15)

  1. Couldn't re-install the MDW support again... says:

    In fact, I tried the script supplied here before I tried yours:…/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)

  2. Please try the solution provided in blog entry on a clean SQL Server installation that has data collector configured.  Other workarounds provided in…/ViewFeedback.aspx may have deleted related schedules while dropping data collector jobs


    Sethu Srinivasan

    SQL Server [MSFT]

  3. Reecha Mishra says:

    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 !!

  4. Salil Gupta says:


    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.



  5. Brian says:

    Thank you.

    Worked fine on our R2 test server.

  6. sp_syscollector_cleanup_collector stored procesure is available in msdb database in SQL 2012 RC0…/future-editions.aspx

  7. Bryan says:

    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.

  8. Bryan says:



    ALTER DATABASE [PerformanceDW]


    DROP DATABASE [PerformanceDW]


    This worked

  9. 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.

  10. Bryan says:

    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.

  11. Joe says:

    Works perfectly, thanks heaps!

  12. Sumedha says:

    Thanks a lot. Script worked on SQL 2008 r2.

  13. Channdeep Singh says:

    Thank you sir. This helped me.

  14. Sylvie Perrault says:

    I ran the script dccleanup.sql and it looks like it worked properly.

    There are a few sysutility jobs left:




    Thank you for the script

Skip to main content