Purge data from SCOM database

I've done this successfully a few times when agents won't connect to mgmt servers or gateways - even after trying the usual (bounce health service or delete folders). As soon as I did the purge (the last query) the agent appeared in the pending queue. After approving the agent, everything was healthy.

I followed this article which doesn't discuss my issue but it was my only real option as logging a case with MS wouldn't really help.

The environment I did this on was SCOM 2016 UR10.

Query 1

First run this query on the OperationsManager db. It is "To inspect the database relationships to see if there is a still a discovery associated with a computer".

DECLARE @name varchar(255) = '%your_agent%'

SELECT BME.FullName, BME.DisplayName, BME.Path, dv.DisplayName AS 'DiscoveryDisplayName', dv.Name AS 'DiscoveryName'

FROM typedmanagedentity TME 

JOIN BaseManagedEntity BME ON TME.BaseManagedEntityId = BME.BaseManagedEntityId 

JOIN DiscoverySourceToTypedManagedEntity DSTME ON TME.TypedManagedEntityID = DSTME.TypedManagedEntityID 

JOIN DiscoverySource DS ON DS.DiscoverySourceID = DSTME.DiscoverySourceID 

JOIN DiscoveryView dv ON DS.DiscoveryRuleID=dv.Id 

WHERE BME.Fullname like @name

ORDER BY BME.FullName, DiscoveryDisplayName

Query 2

Next, get the Base Managed Entity ID of the agent that is fubar from the row that has Microsoft.Windows.Computer:your_agent in the FullName column.

DECLARE @name varchar(255) = '%your_agent%'
SELECT BaseManagedEntityId, FullName, DisplayName, IsDeleted, Path, Name
FROM BaseManagedEntity WHERE FullName like 'Microsoft.Windows.Computer:'+@name -- Don't think you need this. OR DisplayName like @name
ORDER BY FullName

Query 3

Next input that BaseManagedEntityID into this delete statement. This will delete specific typedmanagedentities more gracefully than setting IsDeleted=1.

Change "00000000-0000-0000-0000-000000000000" to the ID of the invalid entity.

DECLARE @EntityId uniqueidentifier = '00000000-0000-0000-0000-000000000000'
DECLARE @TimeGenerated datetime;
SET @TimeGenerated = getutcdate();
BEGIN TRANSACTION
EXEC dbo.p_TypedManagedEntityDelete @EntityId, @TimeGenerated;
COMMIT TRANSACTION

Query 4

Run query 2 again and make sure IsDeleted column = 1.

Query 5

This will give you an idea of how many BMEs are in scope to purge:

SELECT count(*) FROM BaseManagedEntity WHERE IsDeleted = 1

Query 6

This query is for SCOM 2012 but works on 2016. It will purge all IsDeleted=1 objects immediately but will only do 10000 records. If you have more it will require multiple runs.

Normally this is a 2-3day wait before this would happen naturally.

DECLARE @TimeGenerated DATETIME, @BatchSize INT, @RowCount INT

SET @TimeGenerated = GETUTCDATE()

SET @BatchSize = 10000

EXEC p_DiscoveryDataPurgingByRelationship @TimeGenerated, @BatchSize, @RowCount

EXEC p_DiscoveryDataPurgingByTypedManagedEntity @TimeGenerated, @BatchSize, @RowCount

EXEC p_DiscoveryDataPurgingByBaseManagedEntity @TimeGenerated, @BatchSize, @RowCount