- 3 minutes to read

Remove duplicate EndPoints and related metrics

This troubleshooting note shows how to identify and remove duplicate rows in the dbo.EndPoints table and delete any associated LogPoints, MetricsInLogPoints, and MetricValues that reference those duplicate endpoints.

Use this script only after taking a full backup of the database and preferably in a maintenance window or on a staging copy. The script deletes rows and cannot be undone without a backup.

Prerequisites:

  • Verify you have a recent database backup.
  • Ensure you have appropriate SQL Server administrative privileges to DELETE rows and create temporary tables.
  • Prefer running the script on a non-production copy first to validate behavior.

What the script does (high-level):

  • Builds a temporary list of duplicate EndPoints (keeps the lowest EndPointId per unique key).
  • Iterates over each duplicate EndPointId and deletes related MetricValues, MetricsInLogPoints, LogPoints, then removes the EndPoints row.
  • Prints progress and skips any endpoint that raises an error during deletion.
  • At the end, reports any remaining duplicate groups and associated EndPointIds for manual inspection.

Run the following T-SQL from a query window connected to the Nodinite configuration database (for example, Nodinite_Config):

IF OBJECT_ID(N'dbo.EndPoints', N'U') IS NULL
BEGIN
    RAISERROR(N'Table dbo.EndPoints does not exist in this database.', 16, 1);
    RETURN;
END;

IF OBJECT_ID(N'tempdb..#EndPointDuplicates', N'U') IS NOT NULL
    DROP TABLE #EndPointDuplicates;

SELECT
    e.EndPointId,
    e.EndPointTypeId,
    e.LogAgentSourceId,
    e.Name,
    e.Direction,
    e.URI
INTO #EndPointDuplicates
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY EndPointTypeId, LogAgentSourceId, Name, Direction, URI
            ORDER BY EndPointId
        ) AS rn
    FROM dbo.EndPoints
) e
WHERE e.rn > 1;

DECLARE @CurrentEndPointId INT;

WHILE EXISTS (SELECT 1 FROM #EndPointDuplicates)
BEGIN
    SELECT TOP (1) @CurrentEndPointId = EndPointId
    FROM #EndPointDuplicates
    ORDER BY EndPointId;

    BEGIN TRY
        DELETE MV
        FROM dbo.MetricValues MV
        JOIN dbo.MetricsInLogPoints MILP ON MV.MetricId = MILP.MetricId
        JOIN dbo.LogPoints LP ON MILP.LogPointId = LP.LogPointId
        WHERE LP.EndPointId = @CurrentEndPointId;

        DELETE MILP
        FROM dbo.MetricsInLogPoints MILP
        JOIN dbo.LogPoints LP ON MILP.LogPointId = LP.LogPointId
        WHERE LP.EndPointId = @CurrentEndPointId;

        DELETE LP
        FROM dbo.LogPoints LP
        WHERE LP.EndPointId = @CurrentEndPointId;

        DELETE FROM dbo.EndPoints
        WHERE EndPointId = @CurrentEndPointId;

        PRINT CONCAT('Deleted duplicate endpoint Id=', @CurrentEndPointId);
    END TRY
    BEGIN CATCH
        PRINT CONCAT('Skipping EndPointId=', @CurrentEndPointId, ': ', ERROR_MESSAGE());
    END CATCH

    DELETE FROM #EndPointDuplicates
    WHERE EndPointId = @CurrentEndPointId;
END

;WITH RemainingDuplicates AS (
    SELECT
        EndPointTypeId,
        LogAgentSourceId,
        Name,
        Direction,
        URI,
        COUNT(*) AS TotalRows
    FROM dbo.EndPoints
    GROUP BY EndPointTypeId, LogAgentSourceId, Name, Direction, URI
    HAVING COUNT(*) > 1
)
SELECT
    rd.EndPointTypeId,
    rd.LogAgentSourceId,
    rd.Name,
    rd.Direction,
    rd.URI,
    rd.TotalRows,
    e.EndPointId
FROM RemainingDuplicates rd
INNER JOIN dbo.EndPoints e
    ON e.EndPointTypeId = rd.EndPointTypeId
    AND (
        (e.LogAgentSourceId IS NULL AND rd.LogAgentSourceId IS NULL)
        OR (e.LogAgentSourceId = rd.LogAgentSourceId)
    )
    AND e.Name = rd.Name
    AND e.Direction = rd.Direction
    AND e.URI = rd.URI
ORDER BY rd.TotalRows DESC, rd.EndPointTypeId, e.EndPointId;

Notes and cautions:

  • This script permanently deletes rows. Always have a verified backup before running.
  • The deletion order removes metric values and log points first to avoid orphaned foreign-key references.
  • If your database has custom constraints or triggers, review and test carefully — triggers may block or alter deletes.
  • If the script skips a row due to an error, record the printed message and investigate the cause (foreign-key violations, permission errors, or locked resources).

If you prefer to only identify duplicates without deleting them, run the SELECT portion that populates #EndPointDuplicates and inspect the results before proceeding.

Next Step