OpsMgrDW Grooming

By Rikard Ronnkvist, 2009-10-24 14:55 OpsMgr

After a while my demo environment with OpsMgr data warehouse (and loads of other stuff) needed some more space.

I haven’t looked in to grooming of the DW before. So as usual Google is a nice friend. :-)

Stefan Stranger has a nice post with loads of grooming information.

So what I did was to download the dwdatarp tool.

First I ran it to see the current status

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW

Looks like most of the datasets are stored 400 or 180 days… That is somewhat to much data for a demo environment.
I took the values and divided them by 4. Then ran the following to free up 75% of the database.

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Alert -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Raw -m 8
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Configuration -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Event -a Raw -m 25
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Daily -m 100

OK… when you have a database full of free space you need to truncate it, but that’s another story…


Uppdatera GroomDays i SCDW

By Rikard Ronnkvist, 2006-11-07 13:50 MOM2005, SQL Server

För att minska ner storleken på SystemCenterReporting-databasen kan man dra ner på antalet dagar man sparar data.

Genom att köra det här SQL-scriptet så drar man ner till 7 dagar.

USE SystemCenterReporting

Declare @Groomdays int
SELECT @Groomdays=7

exec p_updateGroomDays ‘SC_SampledNumericDataFact_Table’, @Groomdays
exec p_updateGroomDays ‘SC_AlertFact_Table’, @Groomdays
exec p_updateGroomDays ‘SC_EventParameterFact_Table’, @Groomdays
exec p_updateGroomDays ‘SC_AlertToEventFact_Table’, @Groomdays
exec p_updateGroomDays ‘SC_EventFact_Table’, @Groomdays
exec p_updateGroomDays ‘SC_AlertHistoryFact_Table’, @Groomdays