How to delete Management Data-ware house Collection


As part of working on Management Data warehouse I come across a situation where I need to remove the instances that are going to get decommissioned.

Initially I thought this would be very straight forward but later I realized there is no relevant solution on the internet.

Okay at first I went on to see what are all the servers that are being added to MDW server. Frankly speaking I was not able to find this initially later I found them. To view the report you need to click on your MDW Database ->reports->

And the data would appear as shown in the image below

As you could see there are nearly 6  to 7 instances out of which I need to delete 3 of them. And the procedure to do is

1) Disable Data collection on the instance
2)Clean Up data collectors

The above steps will just ensure that the data would not get uploaded on to your central MDW Server however to remove the records we need to delete the data from the table. So we need to check the data from the below tables and should grab source_id and then perform delete operation. This will help us in removing the entries all together and would make the report looks updated.

select * from core.source_info_internal

select * from core.snapshots_internal

delete from core.snapshots_internal where source_id in (22,23,24,25,26,27)

Note: As part of deletion you can encounter Blocking so for this you need to ensure that you disable the data collection temporarily .