Cleaning stale data out of datawarehouse database

Prior to SCOM 2012 R2 UR7, there was an issue whereby SCOM did not groom out old data from the Event Parameter and Event Rule tables in the DW database.This will show up as these tables growing quite large, especially the event parameter tables. This old, orphaned data will never get groomed out causing DW bloat.

NOTE
ANYONE who ever ran a SCOM management group prior to SCOM 2012 R2 UR7, is affected. And the quantity of the old data will vary depending on how many events you were collecting and storing into your DW.

Once you apply UR7 or later, this is no longer an issue and the normal grooming will groom out the data. HOWEVER – It won’t never go back and clean out the old, already orphaned event parameters and event rules.

For many people this isn’t a big deal, as it just means event reports might run slower but it also means that backups and restores of the db will take longer and it seems pointless  having the extra space being consumed for no reason. If you want to free up some space in your datawarehouse db, read on……….

Run the following SQL query against your datawarehouse database to find out how many events are in scope to be groomed:-

SELECT count(*) from event.vEventParameter ep
WHERE ep.EventOriginId NOT IN
(SELECT distinct EventOriginId from event.vEvent)

select count(*) from event.vEventRule er
WHERE er.EventOriginId NOT IN
(SELECT distinct EventOriginId from event.vEvent)

**Before proceeding make sure you have a recent backup of your database**

The following SQL query will groom out 1000000 rows (you can adjust this) but I found that 1000000 runs pretty quick and doesn’t consume alot of transaction log space:-

 

DECLARE
@MaxRowsToGroom int
,@RowsDeleted int

SET NOCOUNT ON;
SET @MaxRowsToGroom = 1000000

DECLARE
@RuleTableName sysname
,@DetailTableName sysname
,@ParamTableName sysname
,@DatasetId uniqueidentifier = (select DatasetId from StandardDataset where SchemaName = ‘Event’)
,@TableGuid uniqueidentifier
,@Statement nvarchar(max)
,@schemaName sysname = ‘Event’

IF OBJECT_ID(‘tempdb..#Tables’) IS NOT NULL
DROP TABLE #Tables

SELECT RowNum = ROW_NUMBER() OVER(ORDER BY TableGuid)
,TableGuid
INTO #Tables
FROM StandardDatasetTableMap where DatasetId = (select DatasetId from StandardDataset where SchemaName = ‘Event’)

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Tables)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM #Tables)

WHILE @Iter <= @MaxRownum
BEGIN

SET @TableGuid =(SELECT TableGuid FROM #Tables WHERE RowNum = @Iter)

–BEGIN TRY
BEGIN TRAN

SELECT TOP 1 @RuleTableName = BaseTableName + ‘_’ + REPLACE(CAST(@TableGuid AS varchar(50)), ‘-‘, ”)
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = ‘Rule’)

SET @Statement = ‘DELETE TOP (‘ + CAST(@MaxRowsToGroom AS varchar(15)) + ‘)’
+ ‘ FROM ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@RuleTableName)
+ ‘ WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM Event.vEvent)) ‘
execute (@Statement)

SELECT TOP 1 @ParamTableName = BaseTableName + ‘_’ + REPLACE(CAST(@TableGuid AS varchar(50)), ‘-‘, ”)
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 1)
AND (TableTag = ‘Parameter’)

SET @Statement = ‘DELETE TOP (‘ + CAST(@MaxRowsToGroom AS varchar(15)) + ‘)’
+ ‘ FROM ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@ParamTableName)
+ ‘ WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM Event.vEvent)) ‘
execute (@Statement)

SET @RowsDeleted = @@ROWCOUNT

COMMIT

SET @Iter = @Iter + 1
select @RowsDeleted as RowsDeleted
END

IF OBJECT_ID(‘tempdb..#Tables’) IS NOT NULL
DROP TABLE #Tables

NOTE:-There is a link to the SQL query on a word doc at the bottom of this post as a copy and pasting tends not to work from the webpage due to formatting issues

Once you have finished run the following query to update statistics:-

exec sp_updatestats

dwcleanup

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

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