SQL query to find Orphaned Distribution Point References

Below query can be used to identify if there are any orphaned rows left in the database for a DP that is no longer in the environment. There could be orphaned rows if the DP was not removed properly.

 

DECLARE @DPName NVARCHAR(100)

SET @DPName = ‘RemoteDP.KARTHI.COM’

SELECT * FROM ContentDPMap WHERE ServerName = @DPName

SELECT * FROM DistributionPoints WHERE ServerName = @DPName

SELECT * FROM DPInfo WHERE ServerName = @DPName

SELECT * FROM PkgServers_G WHERE NALPath like ‘%’ + @DPName + ‘%’

SELECT * FROM PkgServers_L WHERE NALPath like ‘%’ + @DPName + ‘%’

SELECT * FROM PkgStatus_G WHERE PkgServer like ‘%’ + @DPName + ‘%’

SELECT * FROM PkgStatus_L WHERE PkgServer like ‘%’ + @DPName + ‘%’

SELECT * FROM SysResList WHERE RoleName = ‘SMS Distribution Point’ AND ServerName = @DPName