Delete data from SCOM Ops db

Had an issue with SCOM where an agent had been deleted but still appeared in the Windows Computers view.

I followed this article and ran all queries except 5. After a few minutes the computer disappeared.

In brief you need to run these queries:

First, get the BaseManagedEntityId of the computer:

DECLARE @name varchar(255) = 'COMP_FQDN'

SELECT BaseManagedEntityId, FullName, DisplayName, IsDeleted, Path, Name

FROM BaseManagedEntity

WHERE FullName like @name OR DisplayName like @name

ORDER BY FullName

Note: It's the row with Microsoft.Windows.Computer:COMP_FQDN

Change BaseManagedEntityId  to the ID of the invalid entity:

DECLARE @EntityId uniqueidentifier = 'BaseManagedEntityId'

DECLARE @TimeGenerated datetime; 

SET @TimeGenerated = getutcdate();

BEGIN TRANSACTION 

EXEC dbo.p_TypedManagedEntityDelete @EntityId, @TimeGenerated; 

COMMIT TRANSACTION

Run the first query again. This is to inspect the objects and see if their IsDeleted flag is now = 1. A few hours later we got this error on all mgmt servers:

So I ended up running Query 5 from Kev's article and it fixed it straight away:

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

You will see Event Id 29180 logged saying this:

OpsMgr Management Configuration Service successfully executed 'AgentAssignment' engine work item.