Cleaning up old data from the Data Warehouse database in OM 2007 R2

  • Article

IMPORTANT: Allways perform a FULL Backup of the databases before doing anything to it !!!

 

There are some situations like event storms where you end up havin old entries in the OM 2007 Data Warehouse database. Old data as in data that is older than the grooming threshold. This may happen because if you constantly have floods of event data written to the database, then the grooming procedures just can't keep up because they run on a regular interval but only delete a fixed number of rows per run.

This query may also be very 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: https://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<br>    @MaxDataAgeDays INT,<br>    @DataSetName NVARCHAR(150)<br> SET @DataSetName = 'Event'<br> SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays)<br> FROM StandardDatasetAggregation<br> WHERE DatasetId = (<br>    SELECT DatasetId<br>    FROM StandardDataset<br>    WHERE SchemaName = @DataSetName<br> )<br> SELECT COUNT(*)<br> FROM EventCategory<br> WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM EventChannel<br> WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM EventLoggingComputer<br> WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM EventPublisher<br> WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM EventUserName<br> WHERE LastReceivedDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM ManagedEntityProperty<br> WHERE ToDateTime < DATEADD(DAY, -@MaxDataAgeDays, GETUTCDATE())<br> SELECT COUNT(*)<br> FROM RelationshipProperty<br> 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 OM a little :D

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

 

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

 

If you were experiencing this issue, then you may see much better performance now.

 

CHEERS!