Understanding and modifying Data Warehouse retention and grooming

<!--[if lt IE 9]>


Comments (98)
  1. Anonymous says:

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

  2. Kevin Holman says:

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

  3. Kevin Holman says:

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

    1. @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.

      1. Kevin Holman says:

        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.

  4. Anonymous says:

    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?

  5. Kevin Holman says:

    Eric – when this is really big – it generally means that you have, or had at one time, a MP that was filling the configuration dataset with discovered instance change information.  The older Dell and IBM MP's were very bad about this, as are the Microsoft SQL, AD, DNS, etc…

    If you want to see what is using all the space… you can run the following query against the warehouse DB:

    select distinct top 50 mep.ManagedEntityRowId, me.FullName, Count(mep.managedEntityPropertyRowId) as 'Total'

    from ManagedEntityProperty mep WITH(NOLOCK)

    LEFT JOIN ManagedEntity me WITH(NOLOCK) on mep.ManagedEntityRowId = me.ManagedEntityRowId

    group by mep.ManagedEntityRowId,me.FullName

    order by Total desc

    1. Bryan says:

      How do you remove the data that is no longer relevant?

  6. Kevin Holman says:

    @Oscar –

    Why do you feel you need to modify it?

  7. Kevin Holman says:

    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.

  8. Kevin Holman says:

    @Prashant –

    1.  Yes you can revert back whenever you wish

    2.  Once you change retention, the data will be groomed over time, at the next dataset maintenance operation that runs for that dataset.  You cannot say exactly how long it will take to complete grooming of your adjusted dataset, it depends on many factors.  Normally, if data in a dataset is large and in multiple tables, those tables will be dropped quickly, and you should be able to check for this by running the command line tool and look at %use or quesry/report on the DB itself directly.

  9. Kevin Holman says:

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

  10. Kevin Holman says:

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

  11. Kevin Holman says:

    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.

  12. Anonymous says:

    Thx Kevin. The explanations and examples are very clear.

    Much appreciated,

    John Bradshaw

  13. Anonymous says:

    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

  14. Kevin Holman says:

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

  15. Kevin Holman says:


    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.

  16. Kevin Holman says:

    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?

  17. Anonymous says:


    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.


  18. Kevin Holman says:

    @Paul –

    This is unfortunately normal, especially for small databases like yours.

    The Event collection rules out of the box are quite noisy, and provide very little value.  Also – using old converted MP;s like SMS, SCCM, and the old Exchange 2007 cause even more bloat.

    I recommend setting the event retention in the warehouse to 30 days max – as these provide very little value.  Then – I recommend tuning the worst offendors of event rules:  blogs.technet.com/…/tuning-tip-turning-off-some-over-collection-of-events.aspx

  19. Kevin Holman says:

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

  20. A bit more information on issue. It seems to be AlertStage that is taking up all the space.

  21. Sameer says:

    We have changed all the settings to MaxAge of 90 days, including the Raw Performance Data Set..  

    is this a bad idea?

  22. Bryce says:

    Great article.  Just an FYI to anyone out there, I got an exception error that my remote connection may not be enabled when I first tried to run the tool.  

    I removed the default instance name from the command line, tool ran fine.  

    dwdatarp.exe -s mySQLServerName -d OperationsManagerDW > c:dwoutput.txt

  23. Bryce says:

    FYI – I was running the tool locally on my SQL server.  

  24. Eric says:

    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 ?


  25. oscar says:

    How Can I change the value set for configuration dataset iusing query?


  26. oscar says:

    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.


    I use SCOM 2008 R2 on Windows 2008 R2 and SQL 2008 SP1

  27. oscar says:

    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.


  28. Karl says:


    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?


  29. Garry Trinder says:

    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?



  30. paul says:


    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.



  31. paul says:

    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?



  32. paul says:

    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.


  33. paul says:

    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.



  34. Andrew says:

    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?


  35. Prashant Garad says:

    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?

  36. Prashant Garad says:

    Thanks a lot for your quick response on it….

    It would be very greatful if you provide/suggest the command line tool.

  37. Karthick kesavan says:

    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

  38.  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?

  39. John Nurden says:

    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?

  40. John Nurden says:

    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.

  41. Cameronk says:

    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!

  42. Chris K says:

    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?

  43. Praveen says:

    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

  44. Anonymous says:

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

  45. Samuel Dubrul says:

    Based upon the queries you have provided, I created a small DatawareHouse Query Report MP that lists some important values.

  46. Amy says:

    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?

  47. Techno says:

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

  48. tom says:


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

  49. Mark Ryan says:

    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?

  50. Sunil_03 says:

    Hi Kevin

    We have changed the grooming settings, now what should be done to free up the space which was occupied . Can we go ahead and run the shrink on the SCOM DW database keeping the free space as 40% on DW (recommended).

    Is it recommended.


  51. Morgan Tiley says:

    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.

  52. Kevin Holman says:

    @Morgan –

    I assume you figured out what monitor went crazy on 12/22 and resolved it?

    There is no supported way to just "reset" that I am aware of.

    Why cant you just use the solution I provide here:

  53. Morgan Tiley says:


    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.

  54. Kevin Holman says:

    @Morgan –

    Why did you kill it?  The fact that it was still running shows it is working.
    It might take DAYS to complete…. especially if your SQL server is undersized or if you wrote a REALLY bad monitor that flooded you with state changes. You must process these. Why not just let it run. Once you get past the big flood of data, the rest go really fast.

    I'll repeat my other question: did you figure out what monitor went crazy on 12/22 and resolve it?

  55. Morgan Tiley says:

    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.

  56. Morgan Tiley says:

    Okay, 2 days 17 hours 36 minutes later and it is still running…… Outstanding hourly aggregations increasing.

  57. Morgan Tiley says:

    3 days 16 hours and 57 minutes……and counting

  58. Kevin Holman says:

    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.

  59. Morgan Tiley says:

    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.

  60. Morgan Tiley says:

    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.

  61. Kevin Holman says:

    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.

  62. Kevin Holman says:

    What command are you running in SQL exactly?

  63. Morgan Tiley says:

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

    USE [OperationsManagerDW]
    DECLARE @DataSet uniqueidentifier
    SET @DataSet = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = @DataSetName)

    EXEC StandardDatasetMaintenance @DataSet

    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
    @DatasetId = DatasetId
    FROM Dataset d
    WHERE (d.DatasetDefaultName = ‘State data set’)
    Select AggregationDateTime, AggregationTypeId
    From StandardDatasetAggregationHistory
    Where DatasetId = @DatasetId
    DirtyInd = 1
    Order by AggregationDateTime

  64. Morgan Tiley says:

    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’

  65. Kevin Holman says:

    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. 

  66. Morgan Tiley says:

    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.

  67. Morgan Tiley says:


    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.

  68. Morgan Tiley says:

    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.

  69. 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,

    1. Kevin Holman says:

      No constraints I am aware off. It MIGHT make aggregations run slower, because the RAW queries will take longer when there is more data.

      1. It will for sure take looong 🙂
        Ooops, notifications disabled in my blog profile… that’s why I’m late to your reply…

  70. Kevin Holman says:

    @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. 🙂

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


      1. Another idea would be Jonas IT Service Analytics solution… but without standard reports having the 2y of data.

  71. Tabish says:

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

    1. Kevin Holman says:

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

  72. Sergio says:

    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?

    1. Kevin Holman says:

      Most likely – you don’t have enough rights in SQL. Get an SA to run it.

  73. Tim says:

    Hi Kevin

    Thanks for your ongoing efforts in the SCOM space.

    I got an issue with the dwdatarp.exe, I am using it at a client site which has SCOM 2012 R2 RTM version no patches have been applied, I have been asked to find out the data retention settings, but when I run the dwdatarp.exe I keep getting the following error

    EXCEPTION : System.Data.SqlClient.SqlException: The object ‘[Perf].[PerfRaw_F9C0 4A6DF8924E5C8CED6C83EA5A5A09]’ does not exist in database ‘OperationsManagerDW’ or is invalid for this operation. The object ‘[Perf].[PerfRaw_AD247A11676949FFBC9635A19B8EE0F1]’ does not exist in database ‘OperationsManagerDW’ or is invalid for this operation. The object ‘[Perf].[PerfRaw_5AD5E51A03174D0CAE80BF2C88048865]’ does not exist in database ‘OperationsManagerDW’ or is invalid for this operation. The object ‘[Perf].[PerfRaw_0D5FB852A1294855BBA6BC0C58908F2B]’ does not exist in database ‘OperationsManagerDW’ or is invalid for this operation. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run Behavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe havior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S tring method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.EnterpriseManagement.OperationsManager.ResourceKit.ListDatasetRe tentionPoliciesCommand.Execute() at Microsoft.EnterpriseManagement.OperationsManager.ResourceKit.ListAction.Ex ecuteAction(CommandLineParameterCollection parameters) at Microsoft.EnterpriseManagement.OperationsManager.ResourceKit.ProgramAction .Execute(CommandLineParameterCollection parameters) at Microsoft.EnterpriseManagement.OperationsManager.ResourceKit.DataWarehouse DataRetentionPolicyUtil.Main(String[] args)

    any thoughts what might be the issue?
    Do I need to first apply any Roll up update before I can run this utility?

    1. Kevin Holman says:

      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.

  74. Avinasha says:


    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:\>dwdatarp.exe -s OMDW\i01 -d OperationsManagerDW > c:\dwoutput.txt


    1. Avinasha says:

      When I try
      select * from dataset
      order by DataSetDefaultName
      It again gives empty result!

      This is scary!

  75. Peter says:


    Great post!

    When you disable a collection rule. How long does it take before it grooms out of the DW?

  76. Johnathan says:

    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.

    1. Kevin Holman says:

      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?

  77. Damion S says:

    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#############
    $applicationpath = “$PSScriptRoot\application”
    $DwdatDir = “$applicationpath\SCOMDWGrooming”
    $instance = “SCOM”
    $OperationsDW = “OperationsManagerDW”

    Function Set-DWGrooming{
    [ValidateSet(‘Performance data set’,’Alert data set’,’Event data set’,’State data set’)]
    [ValidateSet(‘Hourly aggregations’,’Daily aggregations’,’Raw Data’)]
    $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
    $loop1 = 300;
    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;
    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 “”
    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”
    $ErrorMessage = $_.Exception.Message
    Write-Host $ErrorMessage

  78. Jorge says:


    Our Event dataset has gone huge

    Event data set Raw data 60 1,014,208,768 ( 97%)

    due to DNS servers issue that has already been solved.

    My question is, do I have to wait for the MaxDateAgeDays of 60 days to those events been groomed or it’s possible to remove all those useless events from the DW?

    Thank you.

    1. Kevin Holman says:

      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.

  79. Anonymous says:
    (The content was deleted per user request)
  80. Maximus_09 says:

    Hi Kevin

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

  81. Anonymous says:
    (The content was deleted per user request)
  82. Anonymous says:
    (The content was deleted per user request)
    1. Vaibhav_Goel says:

      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 dataset

      Process Monitoring: Performance Metric State data warehouse dataset

      Process Monitoring: Performance Metric State data warehouse dataset

      Process Monitoring: Process Health State data warehouse dataset

      Process Monitoring: Process Network Ports data warehouse dataset

      Client Monitoring DataSet

      Configuration DataSet

      Vaibhav Goel

  83. 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…

Comments are closed.

Skip to main content