none
database size limits for Operations Manager and Operations Manager Data Warehouse? RRS feed

  • Question

  • What parameters should be set for database size limits for operations manager database and operations manager datawarehouse database?

    Datawarehouse-MOMMW_Data has autogrowth by 10% with unrestricted growth; MOMDW_log has authogrowth by 10% to 2097152MB.

    Operations Manager-MOM_data has autogrowth with unrestricted growth; MOM_log has authogrowth by 10% to 2097152MB.

    We have limited space on the local drive so how can I limit the growth of databases?  What are the consequences of limiting database growth?,... will the database stop functioning when the database size limit is reached??

    let me know.

     


    dsk
    Tuesday, August 23, 2011 2:01 PM

Answers

All replies

  • I basically want to control or limit the database size.
    dsk
    Tuesday, August 23, 2011 3:02 PM
  • Hi dsk.  You can set the autogrow to a limited size of course to prevent it from out-growing your disk space. Next to that check how full the database is with data. ideal would be more than 40% free space in the operational database. You shouldnt let your operational database get too large as well. up to 30 GB I would say. and preferably still with 40% free space. Also because of some actions running in the background it needs some free space inside the database. If you think you are collecting too much data for the database space you have available you can do some checks (check blog of Kevin Holman for lots of entries) and you can of course disable performance collection rules that you dont need. This helps. For the datawarehouse you can set the groomdays to some time earlier (default is that some stuff gets kept for 400 days while you perhaps want to keep it for 3 months for quarterly reporting). The operationsmanager database also has default of 7 days of data, which you can change through the scom console (administration - settings - database grooming). For large environments (or small ones without space on disk) you could change the defaults of 7 back to 4 days for instance. This will clear up some space inside the database so it doesnt need to grow so much.
    Bob Cornelissen - BICTT (My BICTT Blog) - Microsoft Community Contributor 2011 Recipient
    Tuesday, August 23, 2011 5:02 PM
    Moderator
  • So if I limit the database size of both the operations manager and operations manager dataware house databases, what are the consequences to this limitation?,...  Is there a performance issue for the ops manager database?? Is the a any possibility that the database no longer has room to store alerts efficiently??,... would this cause the database to dismount??  What can cause the ops manager database to dismount?

     

    the ops manager datawarehouse database seems to be getting larger with time so would limiting this database size effect reports being run?

     

    Thanks.


    dsk
    Wednesday, August 24, 2011 3:31 AM
  • The limit for the datawarehouse has more to do with disk space in most cases. It depends on how big your environment is. If below 1000 agents for instance it is still manageable. Its as simple as that a bigger dataaset will take a bit longer to filter the data and render a report for you. The datawarehouse will keep growing until it is about 400 days old, but also if you add agents (more data) and add management packs (more counters colleted).

    For the operational database this has to do with supported limit (30 GB database with 40% free space). It will not crash and burn if you go over it, but it will surely slow down a lot. giving performance issues in every part of scom (like storing data, retrieving data by RMS to calculate health state, the console slowing and so on). It will not dismount. Lets just say that you need to keep an eye on how big the database is and how much data is inside of it. A smaller db will react faster. Some hosted SCOM for instance have turned off almost all performance counter collection rules, which saved a LOT of data in both databases. They also need to do that because they are going towards thousands of agents.

    If you limit growth posibilities for the database and you keep filling it with more data it can become filled up and slow down and perhaps some grooming actions can not be performanced (making the problem worse).

    For instance take a look at these posts as a start:
    http://blogs.technet.com/kevinholman/archive/2008/11/04/boosting-opsmgr-performance-by-reducing-the-opsdb-data-retention.aspx

    http://blogs.technet.com/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx

    http://technet.microsoft.com/en-us/library/cc540407.aspx

    Keeping databases lean while still monitoring what you need is the whole trick.

     


    Bob Cornelissen - BICTT (My BICTT Blog) - Microsoft Community Contributor 2011 Recipient
    Wednesday, August 24, 2011 7:05 AM
    Moderator
  • Hi

    It is the wrong way around to try and control the size of a SQL database by setting the limits on how much it can grow or its maximum size. The database needs to be of sufficient size to store all the data that you need. If it isn't then SQL can't store any more data and depending on the database either monitoring stops (for the Operations Manager database) or you don't get any new reporting data (for the operations manager dw) - SQL databases don't dismount (which is more Exchange terminology) but if there is no space available then they can't store any more data.

    Consider how long you need to keep reporting data and set appropriate values there:

    http://blogs.technet.com/b/kevinholman/archive/2010/01/05/understanding-and-modifying-data-warehouse-retention-and-grooming.aspx

     And a good troubleshooting guide:

    http://blogs.technet.com/b/kevinholman/archive/2010/10/05/uh-oh-operations-database-is-full.aspx

    Cheers

    Graham


    New SCOM 2012 Blog! - http://www.systemcentersolutions.com/blog/
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Wednesday, August 24, 2011 7:11 AM
    Moderator
  • Exactly, so just check what your current status is and what changes you expect (adding more agents soon will increase the amount of data for instance). Check that the databases are healthy. If you are growing out of system limits (disk space) you can take actions (for instance monitoring less or keeping data for a shorter time). As long as everything is healthy and responsive (console, reporting) and you have no further alerts towards this point you will be fine. Like Graham also explained, if you try to control the database size by giving it disk space limits and it just keeps on storing more data something will go wrong.
    Bob Cornelissen - BICTT (My BICTT Blog) - Microsoft Community Contributor 2011 Recipient
    Wednesday, August 24, 2011 7:26 AM
    Moderator
  •  

    Hi,

     

    In addition, I would like to share the following posts about data warehouse grooming for your reference:

     

    Everything you wanted to know about OpsMgr Data Warehouse Grooming but were afraid to ask

    http://blogs.technet.com/b/stefan_stranger/archive/2009/08/15/everything-you-wanted-to-know-about-opsmgr-data-warehouse-grooming-but-were-afraid-to-ask.aspx

     

    Understanding and modifying Data Warehouse retention and grooming

    http://blogs.technet.com/b/kevinholman/archive/2010/01/05/understanding-and-modifying-data-warehouse-retention-and-grooming.aspx

     

    Hope this helps.

     

    Thanks.


    Nicholas Li - MSFT
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, August 25, 2011 3:17 AM
    Moderator