Uninstalling the MDW

In case we move the SQL instance to another machine, we need to uninstall the MDW and then reinstall it because some of the components store the name of the machine. The following T-SQL script will clean up what we set up using the script in the previous post.

 
-- disable collector

exec msdb.dbo.sp_syscollector_disable_collector

-- reset entries

update msdb.dbo.syscollector_config_store_internal
set parameter_value = null
where parameter_name in (
'CacheDirectory'
,'MDWDatabase'
,'MDWInstance'
)

-- reset job ids so jobs can be deleted

update msdb..syscollector_collection_sets_internal
set collection_job_id = null
,upload_job_id = null

-- drop the mdw database

drop database msmdw

-- drop role

use msdb
alter role dc_report_reader drop member [guest]
drop role dc_report_reader

-- get a copy of the collector schedules because they get dropped when the jobs are dropped

select * into #copy from msdb.dbo.sysschedules where schedule_id in (1,4,7)

-- drop jobs

exec msdb..sp_delete_job @job_name = 'collection_set_1_noncached_collect_and_upload'
exec msdb..sp_delete_job @job_name = 'collection_set_2_collection'
exec msdb..sp_delete_job @job_name = 'collection_set_2_upload'
exec msdb..sp_delete_job @job_name = 'collection_set_3_collection'
exec msdb..sp_delete_job @job_name = 'collection_set_3_upload'
exec msdb..sp_delete_job @job_name = 'mdw_purge_data_[msmdw]'

-- copy the schedules back

set identity_insert msdb.dbo.sysschedules on
insert msdb.dbo.sysschedules( 
schedule_id
, schedule_uid
, originating_server_id
, name
, owner_sid
, enabled
, freq_type
, freq_interval
, freq_subday_type
, freq_subday_interval
, freq_relative_interval
, freq_recurrence_factor
, active_start_date
, active_end_date
, active_start_time
, active_end_time
, date_created
, date_modified
, version_number
)
select * from #copy
set identity_insert msdb.dbo.sysschedules off
drop table #copy

-- reset SSIS package ids

truncate table msdb.dbo.syscollector_tsql_query_collector

To clean up the SSIS package, execute the query below and copy the output in a CMD window on the machine.
Replace server_name with the name of your server

 
select
'DTUTIL /SQL "\Data Collector\Generated\' + name + '" /DELETE /SourceServer server_name'
from msdb.dbo.sysssispackages where description like 'Auto-generated%'

Example

 
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_2_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_1_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_2_Upload" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Upload" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_3_Collect" /DELETE /SourceServer server_name
DTUTIL /SQL "\Data Collector\Generated\TSQLQuery_SERVER_NAME_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_1_Upload" /DELETE /SourceServer server_name