- 5 minutes to read

Manually Removing Old Configuration Data

Keep your Nodinite environment lean and high-performing by proactively cleaning up unused Message Types and Endpoints. This guide empowers you to identify and safely remove outdated configuration data, ensuring your system remains reliable and easy to manage.

✅ Proactively remove unused Message Types and Endpoints
✅ Ensure your system remains lean and efficient
✅ Prevent clutter and improve manageability
✅ Follow step-by-step SQL examples for safe cleanup

Important

Always back up your database before performing any deletions. This guide provides SQL scripts to help you safely identify and remove old configuration data. Do note that these scripts are designed to be run in a test environment first to ensure they meet your specific needs. The data you delete cannot be recovered, so proceed with caution.

Overview

Over time, your Nodinite system may accumulate configuration data that is no longer relevant. Removing unused Message Types and Endpoints helps keep your environment clean and efficient. This page provides clear instructions and SQL examples to help you identify and delete old records, ensuring your system runs smoothly.

Remove Old Message Types

You can remove Message Types that are no longer referenced in the LogPoints table or are older than a specified threshold. The following steps guide you through identifying and deleting these records.

Step 1: Identify Message Types to Delete

Use this SQL script to find Message Types with only old entries in LogPoints:

IF OBJECT_ID('tempdb..#MessageTypesToDelete') IS NOT NULL
    DROP TABLE #MessageTypesToDelete  

CREATE TABLE #MessageTypesToDelete (MessageTypeId INT, LastSeen DATETIME2(7))  

;WITH Filtered AS (
    SELECT 
        LP.MessageTypeId, 
        LP.LastSeen
    FROM LogPoints LP
    JOIN MessageTypes MT ON LP.MessageTypeId = MT.MessageTypeId AND MT.MessageTypeID > 2
    WHERE LP.LastSeen < DATEADD(DD, MT.DaysToKeepEvents * -1, GETDATE())  
),
mts AS (
    SELECT 
        MessageTypeId, 
        MAX(LastSeen) AS LastSeen
    FROM Filtered
    GROUP BY MessageTypeId
),
ValidTypes AS (
    SELECT 
        mts.MessageTypeId, 
        mts.LastSeen
    FROM mts
    WHERE NOT EXISTS (
        SELECT 1
        FROM LogPoints LP
        JOIN MessageTypes MT ON LP.MessageTypeId = MT.MessageTypeId
        WHERE LP.MessageTypeId = mts.MessageTypeId
          AND LP.LastSeen >= DATEADD(DD, MT.DaysToKeepEvents * -1, GETDATE())
    )
)
INSERT INTO #MessageTypesToDelete
SELECT MessageTypeId, LastSeen FROM ValidTypes  

SELECT * FROM #MessageTypesToDelete ORDER BY MessageTypeId

This query finds Message Types with only old LogPoints entries, ensuring you do not remove types still in use.

After identifying Message Types to delete, remove all related records from dependent tables:

DELETE FROM CustomFieldsInMessageTypes WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM [EndPoints] WHERE [DefaultMessageTypeId] IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MessageTypesInLogDatabasesToReindex WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MessageTypesInMessageViews WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MessageTypesInSearchFieldExpressions WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MessageTypesInTransportContracts WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM SearchFieldExpressions WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM StyleSheetsInMessageTypes WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MetricsInLogPoints WHERE LogPointId IN (SELECT LogPointId FROM LogPoints WHERE MessageTypeId IN(SELECT MessageTypeId FROM #MessageTypesToDelete))  
DELETE FROM LogPoints WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  
DELETE FROM MessageTypes WHERE MessageTypeId IN (SELECT MessageTypeId FROM #MessageTypesToDelete)  

DROP TABLE #MessageTypesToDelete

These commands ensure all related configuration data is removed, preventing orphaned records.

Step 3: Remove Message Types Without LogPoints

To delete Message Types that have no entries in LogPoints:

DELETE FROM MessageTypesInMessageViews
WHERE MessageTypeId IN (
    SELECT MT.MessageTypeId
    FROM MessageTypes MT
    LEFT JOIN LogPoints LP ON MT.MessageTypeId = LP.MessageTypeId 
    WHERE LP.MessageTypeId IS NULL
      AND MT.MessageTypeId > 2
)

DELETE FROM MessageTypes
WHERE MessageTypeId IN (
    SELECT MT.MessageTypeId
    FROM MessageTypes MT
    LEFT JOIN LogPoints LP ON MT.MessageTypeId = LP.MessageTypeId 
    WHERE LP.MessageTypeId IS NULL
      AND MT.MessageTypeId > 2
)

This ensures you remove Message Types that are not referenced anywhere in LogPoints.

Remove Old Endpoints

You can also clean up Endpoints that are no longer referenced or are outdated.

Step 1: Identify Endpoints to Delete

Use this SQL script to find Endpoints with only old or missing LogPoints entries:

Important

The following script assumes that the DaysToKeepEvents for Endpoints is set to 92 days (3 months). Adjust the DATEADD function as necessary to fit your retention policy.

IF OBJECT_ID('tempdb..#EndpointsToDelete') IS NOT NULL  
    DROP TABLE #EndpointsToDelete  

CREATE TABLE #EndpointsToDelete (EndPointID INT, LastSeen DATETIME2(7))  

;WITH Filtered AS (
    SELECT 
        LP.EndPointID, 
        LP.LastSeen
    FROM LogPoints LP
    JOIN [EndPoints] EP ON LP.EndPointId = EP.EndPointId AND EP.EndPointId > 0
    WHERE (LP.LastSeen IS NULL OR LP.LastSeen < DATEADD(DD, 92 * -1, GETDATE()))
),
mts AS (
    SELECT 
        EndPointId, 
        MAX(ISNULL(LastSeen,GETDATE())) AS LastSeen
    FROM Filtered
    GROUP BY EndPointId
),
ValidTypes AS (
    SELECT 
        mts.EndPointId, 
        mts.LastSeen
    FROM mts
    WHERE NOT EXISTS (
        SELECT 1
        FROM LogPoints LP
        JOIN [EndPoints] EP ON LP.EndPointId = EP.EndPointId
        WHERE LP.EndPointId = mts.EndPointId
          AND (LP.LastSeen IS NULL OR
          LP.LastSeen >= DATEADD(DD, 92 * -1, GETDATE()))
    )
)
INSERT INTO #EndpointsToDelete
SELECT EndPointId, LastSeen FROM ValidTypes  

SELECT * FROM #EndpointsToDelete ORDER BY LastSeen

This query finds Endpoints with only old or missing LogPoints entries.

After identifying Endpoints to delete, remove all related records from dependent tables:

DELETE FROM CustomFieldsInEndPoints WHERE EndPointId IN (SELECT EndPointId FROM #EndpointsToDelete)  
DELETE FROM EndPointsInMessageViews WHERE EndPointId IN (SELECT EndPointId FROM #EndpointsToDelete)  
DELETE FROM EndPointsInTransportContracts WHERE EndPointId IN (SELECT EndPointId FROM #EndpointsToDelete)  
DELETE FROM MetricsInLogPoints WHERE LogPointId IN (SELECT LogPointId FROM LogPoints WHERE EndPointId IN(SELECT EndPointId FROM #EndpointsToDelete))  
DELETE FROM LogPoints WHERE EndPointId IN (SELECT EndPointId FROM #EndpointsToDelete)  
DELETE FROM [EndPoints] WHERE EndPointId IN (SELECT EndPointId FROM #EndpointsToDelete)

These commands ensure all related configuration data is removed, preventing orphaned records.

Step 3: Remove Endpoints Without LogPoints

To delete Endpoints that have no entries in LogPoints:

DELETE FROM EndPointsInMessageViews
WHERE EndPointId IN (
    SELECT EP.EndPointId
    FROM EndPoints EP
    LEFT JOIN LogPoints LP ON EP.EndPointId = LP.EndPointId
    WHERE LP.EndPointId IS NULL
      AND EP.EndPointId > 0
)

DELETE FROM EndPoints
WHERE EndPointId IN (
    SELECT EP.EndPointId
    FROM EndPoints EP
    LEFT JOIN LogPoints LP ON EP.EndPointId = LP.EndPointId
    WHERE LP.EndPointId IS NULL
      AND EP.EndPointId > 0
)

This ensures you remove Endpoints that are not referenced anywhere in the LogPoints statistics.

Next Step