Cleanup Rows in OpsMgr DB Before Migrating

Before migrating your SCOM2012-environment to a newer version it’s recommendable to cleanup old rows in the OpsMgr DB to speed up the update. Dependable on what hardware you run it should be finished in a few minutes (otherwise longer). See the SQL-scripts below.

NB always make a full backup before running these scripts

Show OpsMgr database cleanup rows

DECLARE @SubscriptionWatermark bigint = 0;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
 
Select COUNT (*)
FROM EntityTransactionLog ETL with(nolock)
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL with(nolock) WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL with(nolock) WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND EntityTransactionLogId < @SubscriptionWatermark;

OspMgr Database cleanup query

DECLARE @RowCount int = 1;
DECLARE @BatchSize int = 100000;
DECLARE @SubscriptionWatermark bigint = 0;
DECLARE @LastErr int;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
WHILE(@RowCount > 0)
BEGIN 
DELETE TOP (@BatchSize) ETL
FROM EntityTransactionLog ETL
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND ETL.EntityTransactionLogId < @SubscriptionWatermark;

SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;
END

Comments