drop data from systemcenterreporting DB for MOM 2005

Question

I have inherited a MOM 2005 environment and its unmanageable. The Onepoint DB is 50 GB and Systemcenterreporting DB is around 700 GB (yes 700 !!) I am going through all the normal cleanup process like changing the period for which SCDW stores data in DB. It was configured 900 days and by changing it 5 days at a time and running grooming I have reached to 370 days. my goal is to keep 100 days worth of data eventually. Right now I am tired of decreasing it 5 days at a time and running grooming. I would like to just drop whole of data and start from scratch, how do I do it.

I do not want to uninstall and reinstall reporting services.

What are my options ?

Answer 1

I had the same problem in MOM 2005 when I first inherited it and was able to over come it manually. However, I don't have a 2005 database to look at, but there is a stored procedure that is performing the groom operation. What I did was script that procedure out, and then you can see the tables that have the data being purged. There is a primary table that holds most of the logging information in it that you could truncate to start from scratch, but I wouldn't recommend it. What I did was write a looping script that reduced the retention period one day at a time and called the grooming procedure until it got down to the number of days I wanted to retain. I think it took 3 days for it to complete, but I never had to monitor its execution or intervene to continue the process. Using this kind of process keeps you from orphaning records since you are using the Microsoft process for grooming the data, just taking control and automating it backwards to the point it is manageable.

Answer 2

Well just followed the tedious process diligently and completed the cleanup.