Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

Remove associated data collector jobs

Remove associated data collector jobs

  • Comments 13

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

 
Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • 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.

    Thanks

    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

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

  • USE MASTER

    GO

    ALTER DATABASE [PerformanceDW]

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    DROP DATABASE [PerformanceDW]

    GO

    This worked

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

  • Thanks!

  • Works perfectly, thanks heaps!

  • Thanks a lot. Script worked on SQL 2008 r2.

Page 1 of 1 (13 items)