Datawarehouse Database Cleanup SQL query

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!!

This article applies to SCOM 2007, 2012 as well as 2016 (haven’t tested 1807 yet).

Somtimes you may have event storms where you end up having old entries in the Data Warehouse database i.e data that is older than the grooming threshold. This may happen because the grooming processes can’t keep up because they run on a regular interval but only delete a fixed number of rows per run.

The following SQL query may also be valuable in case you end up with the issue of SQL Timeouts from the Data Warehouse database when the StandardDataSetMaintenance stored procedure is executed by the RMS.

More on that issue here: http://blogs.technet.com/b/kevinholman/archive/2010/08/30/the-31552-event-or-why-is-my-data-warehouse-server-consuming-so-much-cpu.aspx

To check if this is the case for you, run this SQL Query on the Data Warehouse database:

DECLARE    @MaxDataAgeDays INT,    @DataSetName NVARCHAR(150) 
SET @DataSetName = 'Event' 
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays) 
FROM StandardDatasetAggregation 
WHERE DatasetId = (    
SELECT DatasetId    
FROM StandardDataset    
WHERE SchemaName = @DataSetName ) 
SELECT COUNT(*) 
FROM EventCategory 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM EventChannel 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM EventLoggingComputer 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM EventPublisher 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM EventUserName 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM ManagedEntityProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
SELECT COUNT(*) 
FROM RelationshipProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())

Now if you get any results here it means that you are experiencing the issue. So you might want to clean these up manually to help out SCOM.

So execute this SQL Query on the Data Warehouse database to clean the old entries

DECLARE    @MaxDataAgeDays 
INT,    @DataSetName 
NVARCHAR(150) 
SET @DataSetName = 'Event' 
SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays) 
FROM StandardDatasetAggregation 
WHERE DatasetId = (    
SELECT DatasetId    
FROM StandardDataset    
WHERE SchemaName = @DataSetName ) 
DELETE EventCategory 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) 
DELETE EventChannel 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) 
DELETE EventLoggingComputer 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) 
DELETE EventPublisher
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) 
DELETE EventUserName 
WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) 
DELETE ManagedEntityProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE()) 
OPTION(RECOMPILE) DELETE RelationshipProperty 
WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())
OPTION(RECOMPILE)

After running this query you will hopefully experience better performance.

Thanks to  https://scompanion.wordpress.com/ for initially writing this article.