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
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