Compartilhar via

Understanding and modifying Data Warehouse retention and grooming

  • Artigo

<!--[if lt IE 9]>

<![endif]-->

Comments

  • Anonymous
    January 01, 2003
    Hi Kevin.. is there any documentation which throws light on how data aggregation happens.. what algorithm exactly it follows in different aggregation type (raw, hourly, daily)?

  • Anonymous
    January 01, 2003
    @JohnDoe321 - The DW maintenance operations will begin grooming anything older than retention dates.  Some will drop quickly, some will take some time (days).  This will manifest itself as free sapce in the existing database file. You should NEVER EVER EVER "shrink" a database file that is hosting a SCOM database.  This will cause MAJOR performance issues.  Forcing a SQL shrink operation causes the DB file to shink in size and recover free space in the database, reducing the file size and freeing up space on disk.  While this sounds good - it is BAD.  It causes fregmentation of the DB that cannot be resolved by a simple reindex operation. Again - NEVER shrink an OpsMgr database.  Whatever size it has grown to - leave it that size.  Free space in a DB file is a GOOD thing - backups only back up used space - this doesnt hurt anything.  If you MUST recover the used DB file space, then get with a SQL expert who understand how to create a new file on a new disk and move ALL the data from one DB file to a new DB file, to keep the DB from fragmenting.

  • Anonymous
    January 01, 2003
    @John -

  1.  I don't see how number of agents is relative?  This isn't a sizing conversation as much as it is a retention conversation based on retention SLA's.

  2.  The article above was written for OpsMgr 2007.  Network devices wasn't really lit up until OpsMgr 2012.  This article is based on datasets.  Such as alert, performance, events, availability.  Network devices store data in those datasets just like Windows Computers or Unix/Linux machines.  So I am not sure how that would be relevant?

    • Anonymous
      March 28, 2017
      @Kevin Holman,So it ain't possible split up network device data? Cause my network team doesn't care about how a switch was doing a month ago. They care about how it is doing just now.
      • Anonymous
        March 28, 2017
        If the question is from a DW perspective - when collecting performance data - you can re-write all the perf collections NOT to write to the DW if you don't want reporting on them. But there is no way in the DW design to say "collect data for this device type" for a different retention period.

  • Anonymous
    January 01, 2003
    I have set the dw parameters following your instructions. so will the dw db automatically shrink itself down now? or do I need to shrink it and make it smaller?

  • Anonymous
    January 01, 2003
    The comment has been removed

    • Anonymous
      June 30, 2016
      How do you remove the data that is no longer relevant?

  • Anonymous
    January 01, 2003
    @Oscar - Why do you feel you need to modify it?

  • Anonymous
    January 01, 2003
    Ouch... Paul are you on R2 or SP1? You might be impacted by a lack of grooming due to a future event issue...   You might have to find these in the DB and modify them manually... (very carefully) or open a PSS support case for this issue.

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    @Praveen - Your grooming will occur during standarddatasetmaintenance. There are limits to how much will groom out per job, and this varies per dataset you changed from 400 to 90. So it will start "immediately" however it might take 24 hours to recognize the bulk of the grooming, or even longer if you have much to groom.

  • Anonymous
    January 01, 2003
    @Mark -

    Because of the way we store data in tables, and drop entire tables for grooming, it "should" be ok to do it all at once. You might see some data warehouse timeouts on the maintenance, but keep an eye on it. You might have to modify the registry setting to allow a longer timeout to let it finish, as documented: http://blogs.technet.com/b/kevinholman/archive/2014/06/25/tweaking-scom-2012-management-servers-for-large-environments.aspx

  • Anonymous
    January 01, 2003
    How many event tables do you have present? When there is more than one table - we groom by waiting until ALL the data in the table is older than the grooming retention, and then dropping the table. Whee there is only one table (smaller agent counts or well tuned event collection rules) then we use something like "delete from where date > retention" which is FAR less efficient. Just looking at your oldest and newest events doesnt tell the story.  It could be totally normal. There is no warehouse upgrade from MOM 2005.  Since you have events inserted before SCOM even existed, and future events from 2036 - I would say you have time synch maintenance issues in your evnironment - and this will wreak havoc on all kinds of applications.  You need to find out the source of your time synch issues and get those resolved.  I would not worry about your event dataset.... set it to something very short, like 30 days or so - and give it time to groom out (assuming a single event_(guid) table). Only worry about the event dataset when it is the largest dataset in your warehouse - and then dont assume grooming is broken - focus on reducing the retention, reducing the event collection rules by tuning (another blog post of mine).  There were some issues with grooming events in the warehouse if I recall correctly - when future dates were inserted.  I am thinking this was fixed in SP1 or R2 - cannot remember - you should research that.

  • Anonymous
    January 01, 2003
    Thx Kevin. The explanations and examples are very clear. Much appreciated, John Bradshaw

  • Anonymous
    January 01, 2003
    thank you for you reply i look at blogs.technet.com/.../what-is-config-churn.aspx to evaluated my config churn and i come back if i have another problem. for information, i am R2 scom with actually 1000 agents

  • Anonymous
    January 01, 2003
    @Kelly - I'd run the top tables query posted on my SQL blog, or in SQL studio - run the report.  If alertstage is your biggest - I'd wonder if you have a maintenance issue going on.... alerts are synchronized from the opsDB to the staging table in the warehouse, where subsequent jobs manage the data (new and changes) and insert it into alert tables.  I'd say you are experiencing some issues with getting WAY too many alerts (from some sort of flip-flopping monitors).  I'd query the alertstaging tables and see if you can find similarities and focus on the top offending item.  I'd also look at the events on all management servers for warehouse specific events to see if there are issues that are know to the MS.  

  • Anonymous
    January 01, 2003
    Possibly. For the Hourly and Daily - that is fine - if you never need to report on longer than 90 days. We dont recommend modifying the raw data from 10 days to 90 days.  For two reasons:  First - raw data is HUGE, and no built in reports access it.  Second - it could potentially affect the aggregation jobs if it gets too large. Normally - my recommendation is to leave raw alone at 10 days - then set the hourly and daily based on your business requirements - understanding that you can keep daily data for a very long time with minimal storage requirements.

  • Anonymous
    January 01, 2003
    The configuration dataset contains all the discovered properties of all your managed entities, groups, etc. This can be quite large, especially if you are not on OpsMgr 2007 R2 - as there was a bug where we dont groom the config data in SP1 which was fixed in R2.  It can also be large, even in R2 - if you have a lot of poorly written MP's which have discoveries that change frequently - the warehouse keeps a record of the changes necessary for historical reports.  This is another reason config churn is such a bad thing.  The data will mostly be contained in the ManagedEntityProperty table. I would just live with the size issue, and work to ensure you are fixing any config churn issues in your environment.   Are you SP1 or R2?  Have you evaluated your config churn?

  • Anonymous
    January 01, 2003
    Kevin, Hoping you can help us with our constantly growing OpsMgrDW db.  Consuming about 1GB per day.  Installation has been in place for a year. Used your article regarding dwdatarp and found that performance data set was killing us.  Adjusted raw data retention to 1 day, currently using 370 GB.  Adjusted hourly aggregations to 2 days, using 255 GB.  Adjusted daily aggregations to 30 days, using 26 GB. Not content with having that much raw data and not using that data currently, wanted to trim that back.  I ran the following I found in your common sql queries article.  (I hope this was the correct step to take next?) select top 20 pcv.ObjectName, pcv.CounterName, count (pcv.countername) as Total from performancedataallview as pdv, performancecounterview as pcv where (pdv.performancesourceinternalid = pcv.performancesourceinternalid) group by pcv.objectname, pcv.countername order by count (pcv.countername) desc Found that my top 3 counters were: Memory Pages/sec = 2687369 Health Service agent processor utilization = 2508399 Network Interface Bytes Total/sec = 2164085 Decided we did not care about "Memory Pages/sec" perf data for our environment.  So I disabled Memory Pages Per Second for all objects of class Windows Operating System.  I have waited a couple weeks now, and when I run the query above, Memory Pages/sec is still the top counter listed. Am I investigating the top offenders for large Raw Performance data set correctly to reduce that?  Am I not turning off the counter correctly? MUCH APPRECIATED, your articles are phenomenal. Brian

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    @Karl - What is the problem?  It is totally normal for the Perf Hourly tables to consume the largest percentage of a warheouse.  32GB of perf hourly data is not really all that much.  As to tuning this - you should look at this in two dimensions:

  1.  Retention - set the retention to what is in your SLA for reporting.  You are at the default - which is 400 days.
  2.  Tuning.  Run the performance counter queries at blogs.technet.com/.../useful-operations-manager-2007-sql-queries.aspx and determin which ones you dont need - find those rules that collect them - and disable them.  Collecting perf data for all objects - when it is a counter you dont care about - will just fill your warehouse with noise.

  • Anonymous
    January 01, 2003
    A bit more information on issue. It seems to be AlertStage that is taking up all the space.

  • Anonymous
    January 08, 2010
    We have changed all the settings to MaxAge of 90 days, including the Raw Performance Data Set..   is this a bad idea?

  • Anonymous
    February 01, 2010
    The comment has been removed

  • Anonymous
    February 01, 2010
    FYI - I was running the tool locally on my SQL server.  

  • Anonymous
    July 15, 2010
    Hello Kevin, the dwdatarp.exe tool return me a configuration dataset to 46% (the current size is 103,508,496 kb) what are the data in the configuration dataset ? Thanks

  • Anonymous
    January 12, 2011
    How Can I change the value set for configuration dataset iusing query? Thanks

  • Anonymous
    January 13, 2011
    Hi Kevin, I't s requirement from a client, they have another SCOM MG and want to replicate the same configuration that they have in the other MG.  I was trying to use the DWDATARP.EXE tool, but it fails, so I made the changes with some queries, just I need to modify the last one configuration dataset. Thanks I use SCOM 2008 R2 on Windows 2008 R2 and SQL 2008 SP1

  • Anonymous
    January 13, 2011
    Sorry is SCOM 2007 R2 on Windows 2008 R2 and SQL 2008 SP1 every time when I try to use the DWDATARP.EXE tool,  I got this. EXCEPTION : System.Data.SqlClient.SqlException: Cannot open database "Operations ManagerDW - ds "Client Monitoring data set" -a "Daily aggregations" -m 120" requ Login failed for user 'RESTusr-ogarza'.   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception , Boolean breakConnection) And the account that i'm using has Administrative righhts and SA rights in SQL, so I don't know why is not working fine the tool. Thanks.

  • Anonymous
    March 16, 2011
    kevin that dwdatarp.exe tool is giving me this for my hourly performance data set. Performance data set           Hourly aggregations      400    32,945,816 ( 70%) is there a way for me to find whats causing it? thanks

  • Anonymous
    March 28, 2011
    Thanks for the info.  As always useful and timely. How can I tell how long the grooming procedure for a particular dataset is taking?  Also, how can I tell if it is not completing within the configured repeat time?  It seems like a bad thing to have one grooming procedure still running when another one either starts or is supposed to start.  It seems like in that case, the number of records per run is too big. Is there a log of when the grooming procedures run and how much they do? Thanks, Ted

  • Anonymous
    April 11, 2011
    Kevin- Are DW database is about 160 GB. Looking at the information the dwdatarp.exe tool gives us, the Event data set is 80%. Event data set                 Raw data                  30    93,663,928 ( 80%) Is this normal in large DW databases? If not what could we be donig wrong. Thanks! Paul

  • Anonymous
    April 18, 2011
    Thanks Kevin. This helped alot. I think we have another issue. The grooming for the Event data set is not running. After reading many articles on how the grooming process works, I found an article to manually run the grooming process. I am running the following query: DECLARE @DataSet uniqueidentifier SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'event') EXEC StandardDatasetMaintenance @DataSet The query completes successfully but nothing happens. I receive the following output: Caution: Changing any part of an object name could break scripts and stored procedures. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. My oldest event is from 2005-04-05 13:07:22.000 and my newest is 2036-03-12 11:11:50.000. I believe this was an upgrade from MOM but that was before my time. Any Ideas? Thanks, Paul

  • Anonymous
    April 19, 2011
    There are 243 Event_ tables. My event data set by far is the largest data set. Dataset name Aggregation name Max Age    Current Size, Kb

Alert data set Raw data                     180      381,560 (  0%) Client Monitoring data set Raw data 30            0 (  0%) Client Monitoring data set Daily aggregations 100           16 (  0%) Configuration dataset Raw data 400    8,002,632 (  7%) Event data set Raw data 30   95,394,944 ( 80%) Performance data set Raw data 10    2,175,408 (  2%) Performance data set Hourly aggregations 60    9,939,264 (  8%) Performance data set Daily aggregations 365    3,124,392 (  3%) State data set Raw data 60       76,216 (  0%) State data set Hourly aggregations 60      444,848 (  0%) State data set Daily aggregations 60       63,160 (  0%) I will look into the grooming events issue with future dates. Thanks for your quick response. Paul

  • Anonymous
    April 22, 2011
    You were spot on. There is a table 'StandardDatasetTableMap' in the DW that has a column 'EndDateTime'. The grooming stored procedure looks at this column and any date > then your retention date will be skipped. I am not sure how the DW partitions the tables but it looks like there are only 40 'active' tables in the StandardDatasetTableMap table. This caused us to have 196 Event.Event_"GUID" tables with no correlating Event.EventDetails_, Event.EventParameter_ and Event.EventRule_ tables. To resolve this we dropped all Event.Event_ tables with no other correlating Event tables. Then we deleted all rows with future events, updated the EndDateTime with the MAX(datetime) from the active event tables, kicked off the grooming procedure and our DW database went from 180 GB to 30 GB. I just wanted to thank you for all your help. Thanks, Paul

  • Anonymous
    November 25, 2011
    I have been advised that you can groom out data for a specific Management Pack only.  The guy who told me this "can't remember how" but said that he did it :-) Is this possible, and if so, are there any examples on how to target data from certain packs when grooming? Thanks

  • Anonymous
    April 11, 2012
    Hello Kevin, Thanks a lot for such detail descritption.... But I have 2 queries on it:

  1. Can we revert back the retention period if we change for any of the dataset (Like event)?
  2. Once we changed the retention period when data will be groomed. Basically I am planning to change the retention period for Event Dataset and once the data groomed, change back to its original setting. So is it possibel?

  • Anonymous
    April 12, 2012
    The comment has been removed

  • Anonymous
    May 06, 2013
    Hi kevin, im a newbie if i wanna know indepth of reporting and TSQL from where i have to start pls help me!!!! thanks i n advance

  • Anonymous
    August 01, 2013
     I've run into a rather odd issue. We've building out a new SCOM 2012 sp1 system and all was fine until we added around 400 new clients in a week. Were now at 715 total. The next week our Data warehouse size exploded. The strange part about it is the space is being used up by the Configuration Dataset. Its currently taking 85% of the space at nearly 300g. Any idea on what might be causing this or if we can tame it down?

  • Anonymous
    August 14, 2013
    Great article Kevin. A couple of things from me...

  1. how many clients were you basing your sizes on?
  2. Is this purely for servers? i.e. any network devices included? If not, how would you expect the network devices to effect the overall sizing required?

  • Anonymous
    August 14, 2013
    Kevin, thanks for the very quick reply. Sorry, I have probably taken this article out of context with my questions. Basically I have followed the MS sizing tool and worked out the overall total but its a very general vague total. What I am far more interested in is real world examples of DBs that are groomed and the sizes of them dependant upon whats being monitored, datasets used, number of devices you are collecting data from etc. You published a table which shows actual real world values of current sizes. I was curious as to how many agents have collected that data in your environment. My second question was more a hopeful punt as to if you had experience of the kind of increase in data size that would occur if you were also monitoring x amounts of network devices.

  • Anonymous
    November 22, 2013
    Thanks Kevin! Got my DW at a reasonable level to continue my deployment of OpsMgr Clients. Also, the help you gave me @ the Hyper-V training was tremendous. Once I ran Remove-SCOMDisabledClassInstance all my print server client issues went away! Hope to see you at other MS Events!

  • Anonymous
    December 05, 2013
    How soon after making changes to the data sets do the DB sizes actually shrink?  Is there a DW Maintenance job in SCOM that runs on a schedule that needs to happen first before you see any changes?

  • Anonymous
    February 10, 2014
    Thanks Kevin. Great Article. The problem I am facing is the DW was about to get full. So I created a .ndf file in a different drive. But did not stop autogrowth in the original .mdf file. Now the drive space where the .mdf file lies has 0MB free space left. I have stopped the 'Auto Growth' now. Also set the retention policy for Dataset that was set to 400 to 90 days. My question is: now that I have reduced the dataset retention days, will DW delete the records which are older than 90 days? Regards, Praveen

  • Anonymous
    March 12, 2014
    J’ai eu un problème avec l’espace disque qui contient la base de données OperationsManagerDW. En effet

  • Anonymous
    April 25, 2014
    Based upon the queries you have provided, I created a small DatawareHouse Query Report MP that lists some important values.
    http://dynamicdatacenter.wordpress.com/2014/02/14/scom-data-warehouse-query-reports-management-pack/

  • Anonymous
    July 02, 2014
    Hi Kevin, my client want to store raw performance data for one year, no matter in DB ops or DB warehouse, so that there would have one year raw data showing on dashboard and report. Is that possible? Would there be any limitation and retrictions?

  • Anonymous
    October 13, 2014
    Kevin I must Thank you for such really nice blog. you covered pretty much everything in SQL. I have been doing some research on SCOM DW database grooming. I have configured the grooming setting for different datasets retentions. I am looking for a query to check when did grooming run last time and what was the status.

    I run the query select * from maintenancesetting and I get 3 value. Instance optimization, instance grooming and Config optimization time. Could you please throw some lights what each of this type means and which one I should look for to check for grooming of datasets.

    Thanks for your help again !!

  • Anonymous
    October 15, 2014
    @Techno

    Use OperationsManagerDW;
    With AggregationInfo As (Select AggregationType = Case
    When AggregationTypeId = 0 Then 'Raw'
    When AggregationTypeId = 20 Then 'Hourly'
    When AggregationTypeId = 30 Then 'Daily'
    Else NULL End,AggregationTypeId,MIN(AggregationDateTime) As 'TimeUTC_NextToAggregate'
    ,SUM(Cast (DirtyInd As Int)) As 'Count_OutstandingAggregations'
    ,DatasetId From StandardDatasetAggregationHistory
    Group By DatasetId, AggregationTypeId)
    Select
    SDS.SchemaName
    ,AI.AggregationType
    ,AI.TimeUTC_NextToAggregate
    ,Count_OutstandingAggregations,SDA.MaxDataAgeDays
    ,SDA.LastGroomingDateTime
    ,SDS.DebugLevel
    ,AI.DataSetId
    From StandardDataSet As SDS WITH(NOLOCK)
    Join AggregationInfo As AI WITH(NOLOCK) On SDS.DatasetId = AI.DatasetId
    Join dbo.StandardDatasetAggregation As SDA WITH(NOLOCK) On SDA.DatasetId = SDS.DatasetId And SDA.AggregationTypeID = AI.AggregationTypeID Order By SchemaName Desc

  • Anonymous
    January 14, 2015
    Kevin,..
    is it ok to reduce it down from 400 to 90 days in one step if you dealing with a lot of data? or should it be done in increments? 370 days... 300 days.. etc. so not to overwhelm the grooming procedure?

  • Anonymous
    September 04, 2015
    The comment has been removed

  • Anonymous
    September 04, 2015
    @Sunil_03 -

    A shrink is never a recommended practice.

    http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/
    http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
    http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

  • Anonymous
    January 08, 2016
    I've referenced a lot of your stuff and it has helped but I'm stuck now. My State Hourly and State Daily are backed up. the last time they ran successfully was 12-22. Hourly has 406 outstanding aggregations and Daily has 18. Is there a way to force it up to date? I thought I had read a way to reset it but I can't find it anymore. Any ideas? FYI: I had the Hourly set at 365 but set it back because it was huge and we aren't using the data. Thanks.

  • Anonymous
    January 08, 2016
    The comment has been removed

  • Anonymous
    January 08, 2016
    Kevin,

    I tried that but the maintenance script ran and ran and ran.........and ran. 22 hours later it was still running. So I killed it. At this point every hour sees an increase in the Outstanding Aggregations. I'm still new to this.

  • Anonymous
    January 08, 2016
    The comment has been removed

  • Anonymous
    January 08, 2016
    Didn't think 22 hours was correct. We had a couple of monitors that I have since decided were poisoning our system. One being Cisco UCS. I can start it again and see. I wasn't expecting it to run so long. Is there any way to monitor the progress? A query? Thanks again.

  • Anonymous
    January 11, 2016
    Okay, 2 days 17 hours 36 minutes later and it is still running...... Outstanding hourly aggregations increasing.

  • Anonymous
    January 12, 2016
    3 days 16 hours and 57 minutes......and counting

  • Anonymous
    January 12, 2016
    At some point you might consider opening a support case with Microsoft. You might have just flooded too much data, or your sql server might not have enough IOPS to churn through this. But if you stop it, you gain nothing.

  • Anonymous
    January 12, 2016
    Thanks Kevin. I doubt it is the SQL server. It is pretty hefty. I'm calling MS today. Its causing issues with alerts at this point and that is may main concern right now. I would suspect that if the maintenance was actually working I would see some of the older dates being removed and I have not.

  • Anonymous
    January 12, 2016
    Interestingly enough, when I canceled the maintenance and went to close the query I was asked if I wanted to commit the transactions which I did. After that I checked the outstanding aggregations again and it had gone down by 111. So it was working....slowly. I have restarted the maintenance again and will let it go for a while more.

  • Anonymous
    January 12, 2016
    No - you will NOT see any older dates removed - that is normal and by design. Each run of maintenance handles ONE hourly transaction. The reality is you had a flood of state changes so bad in a single hour (or group of hours - or a day) that processing this hour is killing the maintenance. All this time it is working on a single hour of statechanges. Until it finishes calculating that hour - you wil not see any improvement in the numbers. This is how it works. Once you process through the flood time, the subsequent maintenance runs will run quickly, still only processing a single hour at a time, but you can run it in a loop and catch up quickly.

  • Anonymous
    January 12, 2016
    What command are you running in SQL exactly?

  • Anonymous
    January 12, 2016
    This is the loop I'm using to run maint.

    WHILE (
    (SELECT COUNT(AggregationDateTime)
    FROM StandardDatasetAggregationHistory AS ah WITH(NOLOCK)
    INNER JOIN StandardDataSet AS ds WITH(NOLOCK) ON ah.DatasetId = ds.DatasetId
    WHERE ds.SchemaName = @DataSetName
    AND LastAggregationDurationSeconds IS NULL) > 1
    )
    --PRINT N'Looping.'

    BEGIN
    BEGIN TRANSACTION;
    USE [OperationsManagerDW]
    DECLARE @DataSet uniqueidentifier
    SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = @DataSetName)
    EXEC StandardDatasetMaintenance @DataSet
    COMMIT TRANSACTION;
    END

    And this I'm running to see all of the outstanding aggregations by date and time. This is how I could tell some of them had processed.

    DECLARE @DatasetId uniqueidentifier
    SELECT
    @DatasetId = DatasetId
    FROM Dataset d
    WHERE (d.DatasetDefaultName = 'State data set')
    Select AggregationDateTime, AggregationTypeId
    From StandardDatasetAggregationHistory
    Where DatasetId = @DatasetId
    And
    DirtyInd = 1
    Order by AggregationDateTime

  • Anonymous
    January 12, 2016
    This belongs to the top of the loop but I missed copying it.

    USE OperationsManagerDW
    DECLARE @DataSetName varchar(50)
    -- Set this string to the "SchemaName" value that is behind
    SET @DataSetName = 'State'

  • Anonymous
    January 12, 2016
    DONT use a loop when getting started with this - that is a terrible idea! You didn't tell me you were doing a loop.

    Loops are for when you are basically through the mess - and you want to catch up the quick stuff quickly. You need to run this manually at first - one at a time - to understand how long a SINGLE maintenance run takes. Then - when these runs complete rather quickly you can kick off a loop. This is why yours was never ending. Details like this are kind of important to share. :-) That said - now that you are 111 runs in - you can go back to running the loop.  However - with ZERO understanding how long maintenance actually is taking now - you still haven't learned anything.  Like - is the runaway monitor still present?  What are the most common noisy monitors?  You haven't done any of the necessary research on that yet, so it is likely you might find yourself back in the same problem soon. 

  • Anonymous
    January 12, 2016
    Cisco UCS and Citrix were the making the most noise. I was able to uninstall the Cisco because we have other monitors in place but not the Citrix yet. It will be going away soon so I'll just have to stay on top of it for now.

  • Anonymous
    February 10, 2016
    Kevin,

    The query finally finished. Today. A month later! Actually longer that that because I killed it a few times before the last restart. Patience is a virtue? So.....anyone else reading this: Run it. Keep an eye on it and wait. What a mess I had but the errors in my log appears to have gone away. Thanks for your help.

  • Anonymous
    February 10, 2016
    One last question (I hope). I have one remaining error:Creation of module with CLSID "{C6410789-C1BB-4AF1-B818-D01A5367781D}" failed with error "Illegal operation attempted on a registry key that has been marked for deletion." in rule "CISCO.UCS.R2.f5309e9f6228464d8f1ceea6390e120e.PowerSupplyUnit.General.UnitMonitor" running for instance "PSU 1 (Chassis)" with id:"{2699EA96-43EE-8906-A507-5DCFC6125A9D}" in management group "All Computers".


    I know what it is from (Stupid Cisco UCS). Which is what was flapping to begin with. But I uninstalled the MP and it looks like something stuck.

  • Anonymous
    February 19, 2016
    Hi Kevin,
    why comes that: "Do not modify the performance RAW dataset."?

    Got a customer that requires 2y raw data... I know, big DWH. But besides of that, there should be no constraints, right?

    Thank you,
    Patrick

    • Anonymous
      March 28, 2017
      No constraints I am aware off. It MIGHT make aggregations run slower, because the RAW queries will take longer when there is more data.
      • Anonymous
        July 24, 2017
        It will for sure take looong :-)Ooops, notifications disabled in my blog profile... that's why I'm late to your reply...

  • Anonymous
    February 24, 2016
    @Patrick -

    More just a general recommendation. Most I have seen is modifying to 100 days. Only thing you'd have to keep an eye on, is their aggregations. I don't know if our queries are optimized to sift through that much data, so porting it into aggregation jobs might take longer or time out.... but I don't know of any problems or scenarios where that happened. I cannot imagine trying to actually query that much raw data. I doubt it would be terribly successful. :-)

    • Anonymous
      July 24, 2017
      Yeah... I know...Ended up in developing some ps1 scripts which initially copy the whole SCOM DWH to a custom DWH and then syncs all new data and groom out all (raw) data older than 2y.We'll see how that'll perform in a large scale env...Any better ideas highly appreciated.Best,Patrick
      • Anonymous
        July 24, 2017
        Another idea would be Jonas IT Service Analytics solution... but without standard reports having the 2y of data.

  • Anonymous
    May 14, 2016
    In my case:-After changing the retention period of hourly performance data set from 400 to 100 days, how long will it take to groom and free up space from DW ???

    • Anonymous
      May 16, 2016
      @Tabish - You should see some performance table cleanup within a few hours - it is based on an algorithm for datasetmaintenance. It could be as soon as a few minutes, but within 24 hours you should see significant impact IF you have a large number of perfhourly tables, AND all of the data in each table is older than the retention setting. Running an EXEC sp_updatestats never hurts either after making a big change.

  • Anonymous
    June 07, 2016
    Hi, I am trying to run the tool and I am always getting empty results back (just the headers). Any idea what might be causing it?

    • Anonymous
      June 07, 2016
      Most likely - you don't have enough rights in SQL. Get an SA to run it.

  • Anonymous
    September 19, 2016
    The comment has been removed

    • Anonymous
      September 20, 2016
      Ugh. I cannot imagine running for a long time on SCOM 2012 R2 RTM with no rollups applied. There were a LOT of fixes along the way, and one specifically dealt with grooming. It is possible that you have lots of grooming issues now. I'd absolutely apply UR11 to that environment immediately, then run an update statistics or complete reindex job of the SQL DB's, and wait 24 hours - and see if this error goes away. It looks like the dataset table map is not current due to a grooming issue.

  • Anonymous
    April 05, 2017
    Hi, I tried to check this tool on my new installation. I have already 30GB data in the Data base. But, this is returning empty values. What might be the problem? I had tried it earlier may be a month ago! It was working just fine! Now no response!I ran the below.C:&gt;dwdatarp.exe -s OMDW\i01 -d OperationsManagerDW > c:\dwoutput.txtThanks, Avi

    • Anonymous
      April 05, 2017
      When I try select * from dataset order by DataSetDefaultNameIt again gives empty result!This is scary!

  • Anonymous
    April 27, 2017
    HiGreat post!When you disable a collection rule. How long does it take before it grooms out of the DW?

  • Anonymous
    August 22, 2017
    Hi Kevin need to discuss a thing, I observed the operationsmanagerdw database closely and found that perf.vperfraw table is storing the values upto 6 hrs from the current time for examples if the current time is 1200 hrs and scom monitored the cpu ulization of a server as 92% then this value will get stored in dw databse at 1800 hrs , is there any way to reduce this duration.

    • Anonymous
      August 22, 2017
      RAW perf data is stored for 10 days by default. I doubt the design of the DW allows for keeping data less than 1 day. Is that what you are asking?

  • Anonymous
    January 11, 2018
    Hi Kevin. I created the powershell script below. But my problem is when i do a automated SCOM installation and there are no grooming settings in the database the script will be in a loop until de first Windows server is added to SCOM and start reporting some data. Is there a way to add the data sets to the data base with no SCOM monitored servers. Im not the best Scripter any improvements to the script are welcome.# This script alters the retention thresholds for the most important data sets.# These settings influence the growth and size of the OperationsManagerDW database.# Changelog:# 13/3/2015: Initial version# 29-05-2017: Wait until DB has All Grooming rows##############Change Data Warehouse Grooming settings#############try{ $applicationpath = "$PSScriptRoot\application" $DwdatDir = "$applicationpath\SCOMDWGrooming" $instance = "SCOM" $OperationsDW = "OperationsManagerDW" Function Set-DWGrooming{ Param( [parameter(Mandatory=$true)] [ValidateSet('Performance data set','Alert data set','Event data set','State data set')] [String] $DataSetName , [parameter(Mandatory=$true)] [ValidateSet('Hourly aggregations','Daily aggregations','Raw Data')] [String] $Aggregation , [parameter(Mandatory=$true)] [String] $MaxAge ) $DWgroom = ($object = & $DwdatDir\dwdatarp.exe -s .$instance -d $OperationsDW ).Trim() -replace '\s{5,}',',' | ConvertFrom-Csv -ErrorAction Stop $DataSet = $DWgroom | where "Dataset name" -contains $DataSetName | where "Aggregation name" -eq $Aggregation -ErrorAction Stop if (($DataSetname -eq "Performance data set") -and ($Aggregation -eq "Raw Data")){ Write-Host "$DataSetname can only contain aggregation type Hourly or Daily aggregations" -ForegroundColor Red } elseif (($DataSetName -eq "Alert data set") -and ($Aggregation -ne "Raw Data")){ Write-Host "$DataSetname can only contain aggregation type Raw Data" -ForegroundColor Red } elseif (($DataSetName -eq "Event data set") -and ($Aggregation -ne "Raw Data")){ Write-Host "$DataSetname can only contain aggregation type Raw Data" -ForegroundColor Red } else{ $loop1 = 300; Do{ $loop1-- write-host "Waiting until database creates $DataSetName - $Aggregation row" Start-Sleep -Seconds 1 $DWgroom = ($object = & $DwdatDir\dwdatarp.exe -s .$instance -d $OperationsDW ).Trim() -replace '\s{5,}',',' | ConvertFrom-Csv -ErrorAction Stop $DataSet = $DWgroom | where "Dataset name" -contains $DataSetName | where "Aggregation name" -eq $Aggregation -ErrorAction Stop } While(($DataSet.'Dataset name' -ne $DataSetName) -and (($loop1 -gt 0))) Write-Host "====================Current Grooming Setting - $DataSetName $Aggregation======================" -ForegroundColor Green $DWgroom | where "Dataset name" -contains $DataSetName | where "Aggregation name" -eq $Aggregation -ErrorAction Stop if (($dataset."Dataset name" -contains $DataSetName) -and ($DataSet."Aggregation name" -eq $Aggregation)){ write-host "$dataSetname exists checking grooming settings" -ForegroundColor Green if ($DataSet."Max Age" -ne $MaxAge){ Write-Host "Data Warehouse grooming for the $DataSetName and Aggregation does not equel $MaxAge updating Max Age" -ForegroundColor Yellow & $DwdatDir\dwdatarp.exe -s .$instance -d $OperationsDW -ds $DataSetName -a $Aggregation -m $MaxAge $loop2 = 150; Do{ $loop2-- write-host "Waiting until database is ready" Start-Sleep -Seconds 5 $DWgroom = ($object = & $DwdatDir\dwdatarp.exe -s .$instance -d $OperationsDW ).Trim() -replace '\s{5,}',',' | ConvertFrom-Csv -ErrorAction Stop $DataSet = $DWgroom | where "Dataset name" -contains $DataSetName | where "Aggregation name" -eq $Aggregation -ErrorAction Stop } While(($DataSet.'Max Age' -ne $MaxAge) -and (($loop2 -gt 0))) write-host "Max Age is set checking New Grooming Settings" write-host "" Write-Host "======================NEW Grooming Settings - $DataSetName $Aggregation=======================" -Fore Green $DWgroom | where "Dataset name" -contains $DataSetName | where "Aggregation name" -eq $Aggregation -ErrorAction Stop Write-host "" Write-host "" } else{ Write-host "Data Warehouse grooming for the $DataSetName and Aggregation equel $MaxAge no action needed" -ForegroundColor Yellow Write-host "" Write-host "" } } Else { Write-Host "$DataSetname does not exsist in database wait for it to be created." -ForegroundColor Yellow } } } ############################################################## Set-DWgrooming "Performance data set" "Hourly aggregations" "30" Set-DWgrooming "Performance data set" "Daily aggregations" "90" Set-DWgrooming "Alert data set" "Raw Data" "30" Set-DWgrooming "Event data set" "Raw Data" "2" Set-DWgrooming "State data set" "Raw Data" "30" Set-DWgrooming "State data set" "Hourly aggregations" "30" Set-DWgrooming "State data set" "Daily aggregations" "90" }Catch{ $ErrorMessage = $_.Exception.Message Write-Host $ErrorMessage}

  • Anonymous
    February 21, 2018
    The comment has been removed

    • Anonymous
      February 21, 2018
      You have to wait.However, why even bother with 60 days? Set it to something short, like 5 or 10 days. Do you really report on any collected events? I have almost never seen a custom do this. Don't get stuck in the trap of "we keep stuff because we don't know if we need it or not". Alternatively - to free up the space - set retention to less days then when it flood stopped, let it groom, then bump up retention back if you REALLY need 60 days of events. I set events to 10 days in most of my customers, because reporting on events are usually not needed.

  • Anonymous
    August 12, 2018
    (The content was deleted per user request)

  • Anonymous
    August 12, 2018
    Hi KevinIn our environment "dbo.ManagedEntity.Property" table is growing exponentially and we identified the management pack that's causing the storm and working with the vendor in finding the cause.Need your help in understanding Whats this table is used for and isn't it supposed to be groomed, as we see few years old data in it?How frequently we need to groom this table and do we need to do it manually?Thanks for your help.

  • Anonymous
    November 06, 2018
    (The content was deleted per user request)

  • Anonymous
    November 06, 2018
    (The content was deleted per user request)

    • Anonymous
      November 06, 2018
      Hi Kelvin,Kindly assist as I am trying to modify Grooming for SCOM2016 DW and found few dataset which were consuming good space but not sure what data is actually stores and its impact.Process Monitoring: Performance Metric State data warehouse datasetProcess Monitoring: Performance Metric State data warehouse datasetProcess Monitoring: Performance Metric State data warehouse datasetProcess Monitoring: Process Health State data warehouse datasetProcess Monitoring: Process Network Ports data warehouse datasetClient Monitoring DataSetConfiguration DataSetRegards,Vaibhav Goel

  • Anonymous
    December 14, 2018
    Hello Kevin. Please help me, I am badly stuck in a problem. We are using SCOM 2012 R2. Our OperationsManagerDW size has grown too large. 530 GB for 800 agents... Its grooming is not working. We've set the MaxDataAgeDays of all Datasets to minimum values but the grooming is still not working. There's just 5 GB free space left on the disk. We don't have enough disk space to increase its size... When I try to shrink it, it throws the error: “Shrink failed for DataFile 'MOM_DATA'. (Microsoft.SqlServer.Smo): An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo). A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)”. I understand it is unable to shrink since there's not sufficient free space on the disk.I want to just truncate all the data of OperationsManagerDW by creating a new database file of OperationsManagerDW. I don't need any data from the existing OperationsManagerDW. Please help me...