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 and SCOM 2012

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.

 

Advertisements
This entry was posted in SQL Queries and tagged , , , , , , , , , , , , , . Bookmark the permalink.

One Response to Datawarehouse Database Cleanup SQL query

  1. szzaidi says:

    Reblogged this on Technology and code and commented:
    Real world problems with good old SCOM 2012 R2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s