1.1 Scheduled Task: SystemCenterDTSPackageTask
Schedule: 1am everyday
Check the task if successfully by looking at the field LastResult in Control Panel->Scheduled Tasks.0x0 - successed
0xffff - failed
When failed, you can check Application Log in Control Panel->Administration Tools->Event Viewer.
If it failed with Expired Timeout, try to increase query timeout in MOM SQL Server to aovid this error.
1.2 Check the last completely run of DTS job in OnePoint database
select TimeDTSLastRan from OnePoint.dbo.reportingsettings
1.3 Check DataRetentionDays in OnePoint DB and see how many days data requires to be moved.
select * from OnePoint.dbo.groomingsettings
1.4 May need to add /latency to DTS job to speed up the large data moving if it has been failed a few days.
ex: DataRetentionDays =3 and TimeDTSLastRan= 3 days ago,
could set /latency:3
C:\Program Files\Microsoft System Center Reporting\Reporting\MOM.Datawarehousing.DTSPackageGenerator.exe" /silent /latency:3 /srcserver:mom_server_name /srcdb:OnePoint /dwserver:mom_dw_server_name /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager
2. Purge the transferred data in OnePoint DB.
Run SQL Job "MOMX Partitioning And Grooming" to purge the transferred data in OnePoint DB.
3. Run SCDWGroomJob to purge Reporting DB.
3.1 Check Reporting Grooming Day
USE SystemCenterReporting
GO
SELECT CS.TableName AS TableName, WCS.GroomDays AS GroomDays
FROM SMC_Meta_WarehouseClassSchema WCS
JOIN SMC_Meta_ClassSchemas CS
ON CS.ClassID = WCS.ClassID
WHERE WCS.MustBeGroomed = 1
ORDER BY WCS.GroomDays DESC
3.2. Set Reporting Grooming day
--------------------------------------------
USE SystemCenterReporting
GO
Declare @Groomdays int
-- Retain data for 145 days
Select @Groomdays = 145
exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays
exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays
3.3 Start the reporting grooming job (called SCDWGroomJob) in the SQL Agent jobs.
Or run the actual stored procedure:
EXECUTE dbo.p_GroomDatawarehouseTables
If reporting grooming job has been failed for a while, you may have to set Grooming Day to trim the data gradually out of the reporting database.
Usually this job is time-consuming, watch it out after resetting the grooming day in case of the job failure.
