Search

Data Collector: Remove Data Collector to Remove Associated Objects by Lara Rubbelke

Active

93
0
Sign in
 to vote
Type: Suggestion
ID: 334180
Opened: 3/24/2008 11:47:48 AM
Access Restriction: Public
5
Workaround(s)
After the data collector is configured, the data collector can be disabled but not removed. This means that all objects created by the data collector will remain on the instance and certain objects cannot be removed.

When the data collector is disabled, the associated SQL Server Agent jobs and system data collection sets cannot be removed. The jobs will appear as disabled, but SQL Server will not allow you to remove the jobs through T-SQL or through SSMS.

Database Administrators have no option to remove these jobs. If an environment chooses to stop using data collection in the future, they will always have to look around jobs which will never be used on the instance in the future.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

Provide two paths: 1. Disable Data Collection and 2. Remove Data Collection.

Disable Data Collection will disable all of the associated jobs. This is the path when administrators would like to pause data collection but plan to restart data collection at a future date.

Remove Data Collection will remove data collection sets and the associated SQL Server Agent jobs. The associated database must be manually deleted. This is the path when administrators would like to permanently remove data collection on the server.

Benefits

Improved User Interface
Improved Administration

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by RickGot on 2/19/2013 at 2:34 PM
So it looks as though you've created a product, then sold it to DBA's, who subsequently discovered that it provides very little usable information and would like to uninstall but can't. MSFT responses below seem to indicate that you never considered a rollback strategy for this type of implementation. I suppose that indicates that the team that planned and built this invasive product for MS SQL Server probably had very little DBA experience. Sad that MSFT never considered adding a DBA or two to a team building products for use by DBAs. I suppose that's par for the course in Redmond.
Posted by cv2 on 10/26/2012 at 5:19 AM
Sethu, I used the script for SQL versions prior to SQL 2012 and it worked! Thank you very much. -Cindy (cv2)
Posted by Sethu Srinivasan on 7/10/2012 at 12:25 PM
Hello cv2,
New stored procedure is added in SQL 2012,

For SQL versions prior to SQL 2012, please refer to
http://blogs.msdn.com/b/sqlagent/archive/2011/07/22/remove-associated-data-collector-jobs.aspx

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

You can also read through customer feedback in this thread. customers have used the approach mentioned in above blog articles to cleanup collector jobs on their SQL 2008 R2

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by cv2 on 7/6/2012 at 7:40 AM
Is there a cleanup job for SQL 2008 R2?
Posted by Sethu Srinivasan on 2/9/2012 at 10:34 AM
We have added a stored procedure sp_syscollector_cleanup_collector in msdb in SQL 2012.
SQL 2012: http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Thanks
Sethu Srinivasan[MSFT]
SQL Server
Posted by David N Nguyen on 1/18/2012 at 1:48 PM
Thanks Team.
Your script works perfect with no regrettable incident in Test as well as in Production.
But of course, my case at my company may be different than other cases.
I appreciate your effort and genius.
Many thanks.
David N Nguyen.
Posted by Sethu Srinivasan on 7/23/2011 at 10:26 AM
Were you able to cofigure data collection again after cleaning up?

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by eckcessive on 7/22/2011 at 8:19 PM
Thanks Sethu... I ran this on a test server (SQL 2008 R2) with no issues, then I ran it in Production (SQL 2008 R2) with no issues. Thanks for sharing.
Posted by Sethu Srinivasan on 7/22/2011 at 6:28 PM
Hello all,
I have posted a blog entry in our team blog related to this issue. Could you please validate this on your test systems and keep us posted?

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

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by ShishirGS on 7/12/2011 at 9:40 PM
I setup the DCU-08 in our environment. I was able to disable the jobs but not able to Remove it completly. I created a ticket with MS. When we tried to remove it from MSDB db, it results in corruption of the msdb db and finally end up with re-installation of SQL server. So, I will suggest to wait untill MS finally confirmed the Functionality is able to Remove from SQL Server (mostly Sql-2011).
Posted by Geert Vanhove DCOD on 6/27/2011 at 8:01 AM
What is the status of this 2 years after the last post of Microsoft on this?
Is SQL2008r2 not a mojor release?
As many others, I won't even try to test this unless I know I can uninstall it.
Pity, because this was exactly what we needed.
Posted by Chandan jha on 6/23/2011 at 12:08 AM
Do not touch the system tables or views which are used in data collection. I deleted them manually and then when I refreshed the 'management' node in management studio, I was not able to see SQL Server logs, database mail features etc. as the data collection feature got corrupted and started throwing weird errors.
I somehow managed to fix it by using some chunks of scripts from instmsdb.sql which were just related to data collection feature.

Please wait till Microsoft announces the fix in their release. Even if you feel suffocated with the data collection jobs or packages that got created, please leave them.They wont cause any harm and will just sit there in disabled state which is better rather than corrupting msdb.
Posted by Adam Bean on 2/14/2011 at 10:47 AM
Finally decided to give a shot. The workaround was close, but simply the wrong table. This is the query I ran and I was able to successfully drop the jobs:

UPDATE [msdb].[dbo].[syscollector_collection_sets_internal]
SET [collection_job_id] = NULL
,[upload_job_id] = NULL

I do not yet know what if anything this will do in the long run should you want to re-create the process, but I personally don't plan on trying to use it again after this experience. However, I don't believe it will cause an issue as these columns are NULL in this table on a 2008 instance without this enabled.
Posted by baal32 on 1/31/2011 at 10:19 AM
Come on, is this a joke? Two years without being able to provide a simple unisntallation procedure or accepted work-around?

You guys hiring?
Posted by Adam Bean on 10/13/2010 at 1:10 PM
The name posted in the work around is the FK name, not the table name (FK_syscollector_collection_sets_collection_sysjobs). Still do not feel comfortable modifying the system tables. Shame that this was posted over 2 1/2 years ago and no resolution yet.
Posted by Adam Bean on 10/13/2010 at 1:01 PM
Well, doesn't appear that SP2 fixed this issue ... the workaround posted does not appear to be valid either as there is no table by that name.
Posted by FozzieKev on 6/15/2010 at 8:51 AM
This is pretty poor, reminds me of certain brans of Anti-Virus software leaving in hooks all round the system.

Why is there no workaround?
Posted by Kendra Little on 6/2/2010 at 9:51 PM
Also adding my vote for this. I really enjoy the feature, but as with anything it is necessary to be able to uninstall and reinstall it. I currently have an installation of data collector on an R2 server which failed halfway through-- one of the first steps I wanted to take was cleaning up the feature/uninstalling before attempting to reinstall. Very odd not to have that option. A KB with instructions as a workaround would be great.
Posted by cbentle on 3/30/2010 at 12:15 PM
Just wanted to add my vote. I will not be testing Data Collector until there is a resonable process to remove it. And it has been TWO YEARS!!! And it's not in R2???
Posted by Manuel Burggraf on 3/10/2010 at 6:49 AM
Congratulation,
a MS Sales Man just presented this feature to us, I gave it a try and wanted it to be removed... doh.
When reading this here I couldn't believe my eyes.
embarrassing, shame, incompetence, brainless, angry - just a few words on my mind.
I go and reinstall the system.
Thanks a lot - live up to your name
Posted by Adam Bean on 2/23/2010 at 1:18 PM
Is there any update on this? I now have two environments with these jobs that I can not remove because of this error.

Thanks
Posted by Microsoft on 5/29/2009 at 9:29 AM
Lara -

I've resolved this bug duplicate of the work item tracking this work for SQL11. We do plan to make the uninstall experience better for Data Collector in our next major release.

Thanks,

Amy Lewis
Posted by Michael Hotek on 12/16/2008 at 7:32 PM
Same here. We are re-purposing a machine and do NOT want data collection on this machine anymore. The only options I have is to either hack tables in msdb or reinstall the instance. I'm fairly certain that you don't want to send the message to a customer that if you ever turn feature X on, that you can never, ever, remove it without blowing the entire instance away and reinstalling.
Posted by TiborK on 8/25/2008 at 9:41 AM
I fully agree. I now have three instances with DC installed and I will have to clean these up in a week. If that doesn't work, I'm in for a rebuild sys databases situation - not fun. I strongly encourage that a KB is produced with instructions on how to do this. I do not look forward to either doing the rebuild dance or hacking system tables in msdb (being with TSQL or undocumented procedures).
Posted by Kalen Delaney on 4/21/2008 at 4:32 PM
I think the ability to remove a component is crucial. Especially during testing, users are going to want to try out the Data Collection capabilities. and they may decide they don't want it. Right now, there seems to be no way to get rid of all the hooks into the product. This is a bad thing, to leave such clutter lying around. I thought it was just a problem in my pre-release build and that it would be fixed in RTM, but seeing that it won't even be in RTM makes me very uncomfortable and much less likely to recommend this feature set to my customers.

I got a VPC with SQL08 post-CTP6 from a Dev at the MVP Summit, and the name of the Collection Database had been mistyped as MSW instead of MDW. I tried to create a new database and drop the old one, and all my jobs started breaking. I then tried recreating a db of the original misspelled name and it still didn't fix things. That's when I started looking for a way to just remove Collection entirely and found there was no way to do it. This is not good.

Thanks
Kalen Delaney
Posted by Microsoft on 4/10/2008 at 7:38 AM
Hi Lara,
     Thank you for submitting the connect issue for cleaning up the DC components, but we denied the request for the 2008 arelease. We will put together once we have all the data in place so that we can instruct users how to remove the components without causing future problems when users want to trurn it back on. We are defering the real fix until our next major release.
Thanxk you,
Bill Ramos
Sign in to post a workaround.
Posted by Sethu Srinivasan on 4/26/2012 at 3:42 PM
We have added a stored procedure sp_syscollector_cleanup_collector in msdb in SQL 2012.
SQL 2012: http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Thanks
Sethu Srinivasan[MSFT]
SQL Server
Posted by Sethu Srinivasan on 11/11/2011 at 1:37 PM
http://blogs.msdn.com/b/sqlagent/archive/2011/07/22/remove-associated-data-collector-jobs.aspx
Posted by MARTR01 on 7/20/2011 at 8:06 AM
Following on from the posts below, I have found a couple of parmaters in the [msdb].[dbo].[syscollector_config_store_internal] table which reset the data collector to pre-setup. I too do not agree with messing around with system tables but sometimes the reinstall of an instance is not an option and you cant wait until the next release so you have to make the best of what you have. The script below undoes the basic default MDW install, but use with caution.

Very poor show Microsoft!


USE [msdb]
GO

EXEC msdb.dbo.sp_syscollector_disable_collector
GO

UPDATE [msdb].[dbo].[syscollector_collection_sets_internal] SET [collection_job_id] = NULL, [upload_job_id] = NULL
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'collection_set_1_noncached_collect_and_upload')
EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_1_noncached_collect_and_upload', @delete_unused_schedule=1
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'collection_set_2_collection')
EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_2_collection', @delete_unused_schedule=1
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'collection_set_2_upload')
EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_2_upload', @delete_unused_schedule=1
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'collection_set_3_collection')
EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_3_collection', @delete_unused_schedule=1
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'collection_set_3_upload')
EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_3_upload', @delete_unused_schedule=1
GO

UPDATE [msdb].[dbo].[syscollector_config_store_internal] SET parameter_value=NULL WHERE parameter_name='MDWDatabase'
UPDATE [msdb].[dbo].[syscollector_config_store_internal] SET parameter_value=NULL WHERE parameter_name='MDWInstance'
GO
Posted by Chandan jha on 6/22/2011 at 11:47 PM
I really do not agree here. Do not touch the system tables or views which are used in data collection. I deleted them manually and then when I refreshed the 'management' node in management studio, I was not able to see SQL Server logs, database mail features etc. as the data collection feature got corrupted and started throwing weird errors.
I somehow managed to fix it by using some chunks of scripts from instmsdb.sql which were just related to data collection feature.

Please wait till microsoft announces the fix in their release.
Posted by KKarbhari on 9/10/2010 at 7:52 AM
Guys, I found a way to manually clean the Data Collector,

Steps:

1. Disable Data Collector.
2. Go to MSDB Database , replace Job_ID and Upload Job_ID column to NULL in table syscollector_cllection_sysjobs.
3. Now you can remove all your syscollector jobs.
4. Remove syscollector tables from MSDB database (may be in order or Primary Key and Foriegn Key relationship).
5. Finally Drop your Data Collector Database.

It works for me...
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement