Exchange 2010 Reports MP Bug

  • Article

A couple of fellow colleagues in MCS (Paul Bishop & Jason Rydstrand) a while back had identified a significant bug in the latest version of the Exchange 2010 management pack, which will cause severe growth of two tables in the data warehouse DB in an enterprise deployment of Exchange 2010 and Operations Manager.  This affects both OM 2007 R2 and OM 2012.  Here are the specifics: 

The following tables never get groomed out of the data warehouse even though they are set to groom data older than 45 and 3 days respectively:  

  • Exchange2010.ActiveUserStatisticsDailyV14_

  • Exchange2010.ActiveUserStatisticsHourlyV14_

Two Exchange tables share the same Dataset ID and Table GUID. The grooming stored procedure, Exchange2010.Transport_ActiveUserGroom, queries the StandardDataSetAggregationStorage table to get the table name for grooming, but two results get returned and only the last result actually gets groomed.  This issue occurs because we end up grooming the wrong table. This query in the stored procedure returns two records when only one is expected:

SELECT @TableName = BaseTableName FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = @AggregationTypeId)

The first result is the correct table (ActiveUserStatisticsHourlyV14 or ActiveUserStatisticsDailyV14) but the second result is what actually gets set as the table name, which happens to be TenantStatisticsHourlyV14 or TenantStatisticsDailyV14. Because of this we end up grooming the Tenant tables instead of the ActiveUser tables on the following statement:  

DELETE TOP (1000000) FROM [Exchange2010].[TenantStatisticsDailyV14_] WHERE ([DateTime] < CONVERT(datetime, '2012-10-01 00:00:00', 120))

When we should be running the following instead (for daily and hourly):

DELETE TOP (1000000) FROM [Exchange2010].[ActiveUserStatisticsDailyV14_] WHERE ([DateTime] < CONVERT(datetime, '2012-10-01 00:00:00', 120)) DELETE TOP (1000000) FROM [Exchange2010].[ActiveUserStatisticsHourlyV14_

As a workaround until the bug is addressed by the product group, the two delete statements above must be run to groom the tables, moving the date at the end closer and closer to your preferred grooming interval until all the old data is cleared out.  One solution is to change the stored procedure to loop based on the results returned from the select query, but the real issue is likely that two different tables are using the same GUID and the ActiveUser & Tenant tables aren't the only ones have this issue so there is a chance this would need to be done against other tables as well.  Additionally, the logic was expanded and fully tested to incorporate loop logic so that the execution statement within the SP executes against the hourly and daily tables having a base table of both ActiveUserStatisticsV14 and TenantStatisticsV14.

The SQL Scripts attached are available for you to run against your SQL Server hosting the Operations Manager Data Warehouse database to update the stored procedures with the improved logic.

Revised.Exchange2010.Transport_SQLGroomScripts.v2_20140110.zip