Sign in
 
Home2012Previous VersionsLibraryForumsGallery
System Center TechCenter >
> 
> 
DataWarehouse Size Issues

Answered DataWarehouse Size Issues

  • Friday, January 04, 2013 3:06 PM
     
     
    Sign In to Vote
    0
    I've been battling the size of my DataWarehouse for a few weeks now.  The issue appears to be the "Event data set" "Raw data" which is about 75% (110GB) of my collections.  I decreased the Max Age on this data set to 10 days but it still has not resolved the issue.  I ran a SQL query to look for events and event ID 540 (Successful network logon) was at the top of the list with over 17700000 events in my DW.  What can I do about this?
     

All Replies

  • Friday, January 04, 2013 3:11 PM
    Moderator
     
     
    Sign In to Vote
    0

    you have no grooming issues popping up in the logs?

    Perhaps make the jumps in grooming days a little less... just to see if cleaning out data in steps of 5 GB are better than one 100 GB step, because of time-outs, tempdb sizing and so on and so on. SO if it was 400 days, just make it jump down per 20 days each day and see if the amount of data in the table moves down and repeat.

    Also next to the grooming find out if you need those events and check on the collection rules and monitors side if you can find them. Perhaps you can turn a few of them off if they dont seem related to anything. Doesnt help the current contents, but at least the future contents of the database.

    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

     
  • Friday, January 04, 2013 3:17 PM
     
     
    Sign In to Vote
    0

    It is unreasonable huge in DB size of datawarehouse. Per you said,  a lot of events with event id 540 was stored in the DW. first of all, you should find out rule/monitor which has store the event id 540 and disable it.

    Roger

     
  • Friday, January 04, 2013 3:22 PM
     
     
    Sign In to Vote
    0

    you have no grooming issues popping up in the logs?

    How can I determine if I'm having grooming issues?
     
  • Friday, January 04, 2013 3:27 PM
     
     
    Sign In to Vote
    0

    It is unreasonable huge in DB size of datawarehouse. Per you said,  a lot of events with event id 540 was stored in the DW. first of all, you should find out rule/monitor which has store the event id 540 and disable it.

    Roger


    How can I determine which rule/monitor may be storing event id 540?
     
  • Friday, January 04, 2013 3:42 PM
    Moderator
     
     
    Sign In to Vote
    0

    You would have errors in either scom alerts arrising or in the operationsmanager event log on the RMS most likely.
    Kevin Holman also posted a few blog entries about grooming and datawarehouse (google/bing it and you have them).

    So while cleaning up large amounts of data from the database.. just take small steps! If it was 400 days.. first do 5 days at a time, next 10 days at a time, next 15 days at a time (a time = each day). and run those reports where you see the amount of entries in that table and you must see it decrease. dont go down from 400 to 10 days in one step with a large dataset.

    havent looked myself which rule it would be. would probably combine searching by the number in the rules first, next run a report on the events, perhaps you can see where they come from, also search what 540 means actually (lets say it means database-out-of-space (this is not true, just example!!) you could look i this example for free space and database in the rules and most of the times the name of the rules suggest what they do. Those are some ways of trying to find the culprit. Next to of course running sql queries against the datawarehouse and getting them that way. Or try to create an events view in scom and filter it down. Perhaps you can see there where it comes from. Yet another way.

    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

     
  • Friday, January 04, 2013 4:05 PM
    Moderator
     
     
    Sign In to Vote
    0

    I'd also suggest disabling the collection rule - it is a custom rule that someone has created to collect succesful network logon events (SCOM doesn't do this out of the box) ... and not surprisingly, there are a lot of them.

    Go to authoring, find the rules section and do a search ... it doesn't help that we don't know the name so it is a bit hit and hope .. e.g. try 540 or logon

    Cheers

    Graham

    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

     
  • Friday, January 04, 2013 4:15 PM
     
     
    Sign In to Vote
    0

    I'd also suggest disabling the collection rule - it is a custom rule that someone has created to collect succesful network logon events (SCOM doesn't do this out of the box) ... and not surprisingly, there are a lot of them.

    Go to authoring, find the rules section and do a search ... it doesn't help that we don't know the name so it is a bit hit and hope .. e.g. try 540 or logon

    Cheers

    Graham

    Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

    I did a search for 540 and an Exchange (Approaching too many client logons) rule appeared.  Wondering if that's the one...?
     
  • Friday, January 04, 2013 4:55 PM
    Moderator
     
     
    Sign In to Vote
    0
    Open the rules you suspect. Properties. Configuration tab. Find the data source. Click into it. Should see which eventlog and numbers it tries to pickup. Until you find the one you are looking for. Like we say some guesswork in finding it is biggest chance.

    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 + 2013 and Microsoft Community Contributor 2011 + 2012 Recipient

     
  • Saturday, January 05, 2013 11:28 PM
    Moderator
     
     
    Sign In to Vote
    0

    To find the offending rule - create an even view in the Ops Console - under my workspace.  Then give it the criteria of event ID = 540

    Then look at the events - and right click an event and choose show the associated rule.  :-)

    Events should be stored in multiple tables - I believe around 100,000 rows or maybe 1,000,000 ( or more)  I cant recall.  When we groom - it is very efficient because we drop these tables all at once..... based on the newest event in the table being older than the grooming threshold.  So going from 400 days to 10 days in grooming is fine.  Give it a day to groom the data - and look for a dataset maintenance failure in the RMS event log if it doesn't work.  Get more details there.

    If you have events stored in the DB with a future date - this can break grooming as we will never drop the tables... I think this was a bug that was fixed but if they wont groom it is also one thing to keep in mine and investigate.

     

    Kevin Holman http://blogs.technet.com/b/kevinholman

     
  • Wednesday, January 09, 2013 3:03 PM
     
     
    Sign In to Vote
    0
    Thanks Kevin!  I tracked down the associated rule and it appears to be the Logon and Logoff event collection rule.  I didn't know that this rule was enabled by default.  The events were coming in faster than I could keep track of on the screen.
    • Edited by DG1212 Thursday, January 10, 2013 1:54 PM
    •  
     
  • Wednesday, January 09, 2013 5:02 PM
     
     Answered
    Sign In to Vote
    0

    Hi, to speed up grooming process you could also start it manually with respect to necessary dataset

    http://blogs.msdn.com/b/steverac/archive/2007/12/13/scom-2007-operational-and-datawarehouse-grooming.aspx

     
  • Thursday, January 10, 2013 1:52 PM
     
     
    Sign In to Vote
    0
    I've waited 24 hours but have not seen my Event data set decrease in size after turning off the 540 rule.  I tried Alexis article but that did not work.  Is there a way to force grooming on all datasets?
     
  • Thursday, January 10, 2013 3:10 PM
     
     
    Sign In to Vote
    0
    I've waited 24 hours but have not seen my Event data set decrease in size after turning off the 540 rule.  I tried Alexis article but that did not work.  Is there a way to force grooming on all datasets?
    Hi, this method works well in SCOM 2007 and 2012. You should temporary change MaxDataAgeDays for each Dataset to several days then run stored procedure standarddatasetgroom in respect to each Dataset then back the values of MaxDataAgeDays for each Dataset. Then data warehouse DB is groomed but takes the same size on disk and if you need to decrease the size of DB on disk you also should shrink that DB from SQL. DB->Tasks->Shrink->Database.
     
  • Thursday, January 10, 2013 5:27 PM
     
     
    Sign In to Vote
    0

    I've waited 24 hours but have not seen my Event data set decrease in size after turning off the 540 rule.  I tried Alexis article but that did not work.  Is there a way to force grooming on all datasets?

    Hi, this method works well in SCOM 2007 and 2012. You should temporary change MaxDataAgeDays for each Dataset to several days then run stored procedure standarddatasetgroom in respect to each Dataset then back the values of MaxDataAgeDays for each Dataset. Then data warehouse DB is groomed but takes the same size on disk and if you need to decrease the size of DB on disk you also should shrink that DB from SQL. DB->Tasks->Shrink->Database.

    The p_partitioningandgrooming stored procedure works with the OperationsManager DB but I'm unable to run it against the OperationsManagerDW DB.
     
  • Thursday, January 10, 2013 6:39 PM
     
     
    Sign In to Vote
    0

    But in article it says about another stored procedure;) in respect to Data Warehouse. It's standarddatasetgroom.

    also pay attention to other params from [OperationsManagerDW].[dbo].[StandardDatasetAggregation] such as LastGroomingDateTime and so on.

     
  • Thursday, January 10, 2013 7:20 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Read this before you think about shrinking your database.

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

    Jonathan Almquist | SCOMskills, LLC (http://scomskills.com)

     
  • Thursday, January 10, 2013 7:45 PM
     
     
    Sign In to Vote
    0
    Jonathan Almquist you are right to mention this article.