All Spotlight Statistics Repository data for deleted connections is deleted after two months so if you are prepared to wait that long you don't have to do anything. Two months was chosen so that the deleted connections can still be reported on in monthly reports.
However if you cannot wait, then you can run the script below. Because connections names may not be unique (given that Windows and SQL Server default instance connections can have the same name along with replication and analysis services connections) you have to choose the technology type of the connection.
declare @ConnectionName nvarchar(200)
declare @Technology nvarchar(200)
set @ConnectionName = 'ConnectionName'
-- Uncomment one of these
-- set @Technology = 'database/analysisservices'
-- set @Technology = 'database/highavailability'
-- set @Technology = 'database/replication'
-- set @Technology = 'database/sqlazure'
-- set @Technology = 'database/sqlserver'
-- set @Technology = 'os/vmware'
-- set @Technology = 'os/windows'
if @Technology is null
begin
raiserror('A technology must be set', 10, 10)
return
end
Delete from [dbo].[spotlight_perfdata]
where monitored_object_id =
(select monitored_object_id from spotlight_monitored_objects mo where mo.monitored_object_display_name = @ConnectionName
and mo.technology_id = (select technology_id from spotlight_technologies te where te.technology_name = @Technology))
Delete from [dbo].[spotlight_timestamps]
where monitored_object_id =
(select monitored_object_id from spotlight_monitored_objects mo where mo.monitored_object_display_name = @ConnectionName
and mo.technology_id = (select technology_id from spotlight_technologies te where te.technology_name = @Technology))
Replace 'ConnnectionName' with the name of the connection that was deleted and uncomment one of the lines that sets @Technology depending on the type of connection that was deleted.
WARNING: no check is made that the connection has been deleted so care should be taken to put the right connection name into the script !! ALWAYS BACKUP FIRST
