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: 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 OM a little :D

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)

 

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

 

CHEERS!