SCOM DataWare House grooming.

Query to check the size of the tables.

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

Get size of the database.
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
GO

Query to check DW data dates.

Select     min(datetime)as MinDate, max(datetime)as MaxDate ,   datediff(d,min(datetime),max(datetime)) AS NoOfDaysInDataSet from Perf.vPerfHourly

My query showed that there was 424 days of data.

MinDate    MaxDate    NoOfDaysInDataSet
2015-10-09 21:00:00.000    2016-12-06 13:00:00.000    424

Tool for modifying the datawarehouse grooming settings can be downloaded here.

https://blogs.technet.microsoft.com/momteam/2008/05/13/data-warehouse-data-retention-policy-dwdatarp-exe/

Command to run the dwdatarp.exe to get the current sizes of datasets.

C:\temp>dwdatarp.exe -s servername\instancename -d operationsmanagerdw > c:\temp\dwoutput.txt

Dataset name                          Aggregation name     Max Age         Current Size, Kb

Alert data set                             Raw data                           400          104,752 (  0%)
Client Monitoring data set     Raw data                           30              0 (  0%)
Client Monitoring data set     Daily aggregations          400            96 (  0%)
Configuration dataset             Raw data                           400            485,120 (  1%)
DPM event dataset                  Raw data                           400             0 (  0%)
Event data set                           Raw data                           100             12,315,568 ( 14%)
Performance data set              Raw data                           10                4,316,832 (  5%)
Performance data set              Hourly aggregations       400            44,009,336 ( 50%)
Performance data set              Daily aggregations          400            2,049,856 (  2%)
State data set                            Raw data                           180            121,784 (  0%)
State data set                            Hourly aggregations       400           22,979,912 ( 26%)
State data set                            Daily aggregations          400           1,395,216 (  2%)

Changing the grooming settings. Adjust the time according to my reporting requirements.

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Alert data set" -a "Raw data" -m 180

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Performance data set" -a "Hourly aggregations" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Performance data set" -a "Daily aggregations" -m 365

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Event data set" -a "Raw Data" -m 30

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Raw data" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Hourly aggregations" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Daily aggregations" -m 90