Quae Nocent Docent

With our data warehouse approaching the 150 GB limit, I decided it was time to check if everything was ok with it. Obviously there’s very sparse documentation on the inner workings of this important module of SCOM. I must say as usual. In the next few posts I’d like to share with you what I found and the general debugging process for the DW.

But, first of all, I want to make you know that the Windows 2008 KMS MP simply doesn’t groom its data. The MP defines a new data set “KMSEvent” with no aggregation interval, basically we’re talking about events. Alas the MP author simply missed to develop a proper grooming stored procedure. The MP defines this way the new dataset

INSERT StandardDatasetAggregation
(                              
   DatasetId
  ,AggregationTypeId
  ,AggregationIntervalDurationMinutes
  ,AggregationStartDelayMinutes
  ,BuildAggregationStoredProcedureName
  ,DeleteAggregationStoredProcedureName
  ,GroomStoredProcedureName
  ,IndexOptimizationIntervalMinutes
  ,MaxDataAgeDays
  ,GroomingIntervalMinutes
  ,MaxRowsToGroom
  ,LastGroomingDateTime
  ,DataFileGroupName
  ,IndexFileGroupName
)
VALUES (
   ‘$Config/DatasetId$’
  ,0
  ,NULL
  ,NULL
  ,NULL
  ,NULL
  ,’KMS_EventGroom’
  ,$Config/Storage/IndexOptimizationIntervalMinutes$
  ,$Config/Storage/MaxDataAgeDays$
  ,$Config/Storage/GroomingIntervalMinutes$
  ,$Config/Storage/MaxRowsToGroom$
  ,GETUTCDATE()
  ,ISNULL(CAST(NULLIF(‘$Config/Storage/DataFileGroupName$’, ”) AS sysname), ‘default’)
  ,ISNULL(CAST(NULLIF(‘$Config/Storage/IndexFileGroupName$’, ”) AS sysname), ‘default’)
)

Which in turn lead to this row in StandardDataSetAggregation

If you turn on debugging (more on this in the next post) you’ll get this error:

“Failed to groom data for standard data set. Error 2812, Procedure StandardDatasetGroom, Line 146, Message: Could not find stored procedure ‘KMS_EventGroom’.”

Alas this stored procedure doesn’t exist in the MP. Digging into it I found a similar named stored procedure but it has a bad parameter signature, i.e. it cannot be substituted to KMS_EventGroom, more over it is not referenced anywhere in the MP, so from my analysis it’s defined but not used.

CREATE   PROC dbo.KMS_Grooming @groomdays int=90
AS
BEGIN
  SET NOCOUNT ON

declare @Now datetime
declare @Today datetime
declare @TodayMinusX datetime

set @Now = getUTCdate()

So be advised the KMS data currently is not groomed out of the DW, at least not in the intended way.