none
DB Maintenance for OPS MGR DB RRS feed

  • Question

  • Thank you for the response Anders.
    > >
    > > I've read that thread, the issue is that we did not have a maintenance
    > > plan setup and the log file hit a full warning.  We setup a plan to
    > > shrink it some and now we still hit log full warnings and we get db
    > > free space warnings.
    > >
    > > Right now the log file is 1gb - this to me seems extremely large.  Is
    > > this normal?  I am OK with not shrinking the db / log file if this is
    > > just an initial growing point to get it to a stabilized level - we
    > > recenly put more agents on and the log file has filled up.
    > >
    > > I can bump the log file to 2gb and see how it goes but I also am
    > > curious if this log file will grow continuously or if it will
    > > eventyally stabilize.
    > >
    > > Thanks,
    > >
    > > "Anders Bengtsson [MVP]" wrote:
    > >
    > >> Hello Jake,
    > >>
    > >> Please take a look at this thread
    > >> http://www.eggheadcafe.com/conversation.aspx?messageid=31051404&threa
    > >> did=31051404
    > >>
    > >> Anders Bengtsson
    > >> Microsoft MVP - System Center Operations Manager
    > >> www.contoso.se
    > >>> Hello -
    > >>>
    > >>> On our OPSMgr Database, we hit a Logfile Full warning.  Our DB is
    > >>> not set to autogrow, nor is the log.
    > >>>
    > >>> If we set up a Maintenance plan to shrink the db after backup, the
    > >>> DB goes into a 40 % free warning.
    > >>>
    > >>> I am wondering what the Best Practice is for this is?  I can set the
    > >>> autoshrink after backup to be 50% - but this seems like a wasteful
    > >>> practice.
    > >>>
    > >>> Running SQL 2005 64bit on Win2k8 64bit
    > >>> Please advise---
    Monday, June 1, 2009 7:08 PM

Answers

  • Hi

    The SQL transaction log is a sequential record of all changes made to the database (sometimes excluding bulk inserts where it will only record that a bulk insert took place). You shouldn't therefore ever need to change the size of it. BUT .. the size (and what you need to do) does depend on other settings, namely what recovery model are you using for the database?

    Simple recovery means that SQL takes care of the log file size and you can kick back and (largely) ignore it. It does mean that you can't use the transaction log for point in time recovery (or transactional replication \ log shipping) but that is the trade off.

    If you have the database set to Full recovery mode then the log will only shrink when you do backups .. full recovery means that you can recover the database to point in time which means that you need the transaction logs .. which means that you have to back it up. SQL will only clear down the log file size when it knows that a backup has taken place and it is safe to remove the data from the file.

    Might be worth a chat with one of your DBAs to show you where all the settings are.

    Cheers

    Graham
    • Proposed as answer by Dan Rogers Thursday, June 4, 2009 3:29 PM
    • Marked as answer by StuartRModerator Wednesday, August 26, 2009 4:33 PM
    Monday, June 1, 2009 8:28 PM
    Moderator

All replies

  • Hi

    The SQL transaction log is a sequential record of all changes made to the database (sometimes excluding bulk inserts where it will only record that a bulk insert took place). You shouldn't therefore ever need to change the size of it. BUT .. the size (and what you need to do) does depend on other settings, namely what recovery model are you using for the database?

    Simple recovery means that SQL takes care of the log file size and you can kick back and (largely) ignore it. It does mean that you can't use the transaction log for point in time recovery (or transactional replication \ log shipping) but that is the trade off.

    If you have the database set to Full recovery mode then the log will only shrink when you do backups .. full recovery means that you can recover the database to point in time which means that you need the transaction logs .. which means that you have to back it up. SQL will only clear down the log file size when it knows that a backup has taken place and it is safe to remove the data from the file.

    Might be worth a chat with one of your DBAs to show you where all the settings are.

    Cheers

    Graham
    • Proposed as answer by Dan Rogers Thursday, June 4, 2009 3:29 PM
    • Marked as answer by StuartRModerator Wednesday, August 26, 2009 4:33 PM
    Monday, June 1, 2009 8:28 PM
    Moderator
  • Hello,

    I've been running into the same issue myself. Being a DBA, myself, I'm curious what the standard for OpsMgr databases is. For the time being, I have set my data and transaction log files to autogrow up to a certain size. However, I am not too happy with doing that seeing that the OpsMgr installation had specifically set the size of the files and turned off autogrow completely. I wonder if Microsoft intended to do this? If so, perhaps there is something that we don't know and need to revert back to and change something else? Do you know if there are any OpsMgr DBA guides out there that we should be studying up on?
    Monday, June 1, 2009 8:55 PM
  • Hi

    General best practice is to turn off auto-grow and monitor the size of the databases. One reason is that autogrow percentages tend to be so small that over time you can get massive fragmentation and poor performance. From an OpsMgr perspective, the SQL MP doesn't monitor the size of databases set to auto grow which is another reason to turn off this setting.

    Cheers

    Graham
    Monday, June 1, 2009 9:01 PM
    Moderator
  • You should never enable autoshrink of an opsmgr DB or log.

    The DB is designed to require a minimum of 40% free space at all times. 

    Autogrow should only be enabled as an insurance policy - nothing more.

    You should calculate the database size using the widely available estimation tools, or look at what you use in production.  As long as there is no alert storm, change in agent count, or change in management packs - the opsDB size should stay fairly constant.  You should set the DB size to allow for 50% free space RIGHT NOW, and bump it up higher to accomodate for any growth that may occur.

    A good rule of thumb is to set it to 30GB in size for just about any implementation.  Set it higher if your OpsDB is larger than 10GB in used space right now.  That said - unless you have a huge agent count... most implementations will/should never grow beyond 25GB in used space in the database.

    A good rule of thumb for the transaction log is between 20% and 50% of the DB size.  The smaller the DB (1 to 10GB) use the 50% rule.  The large the DB (30-50GB) you cvan likely use the 20% rule.  The in between area is... well, in between.


    A 1GB transaction log is NOTHING.  When I set up my customers initial size - I like to start with a 30GB DB size, and a 15GB transaction log size - using Simple Recovery model.

    I will enable autogrow - but only as an insurance policy of the customer not monitoring and taking action when/if the DB fills up for some reason.  If I do enable autogrow - I ALWAYS limit autogrowth to 80% of the DB volume size, and I grow in set amounts of GB, not %.
    • Proposed as answer by Dan Rogers Thursday, June 4, 2009 3:30 PM
    Tuesday, June 2, 2009 4:59 AM
    Moderator
  • Hi Kevin

    I would actually take the opposite approach on the autogrow - always disabling it, though for the very same reason that you give for enabling. In my view if a customer cannot monitor it properly then I'd prefer OpsMgr to do it. And by enabling autogrow, OpsMgr won't monitor the size. I wouldn't see autogrow as an insurance policy ... unless people read the small print (which you do mention - limit autogrow and have big chunks of growth). The other problem with autogrow is that you cannot specify when (what time of day) to do the growth.

    The Recovery Model to choose depends on what sort of DR is in place (if there is any!!). Obviously log shipping and replication type strategies require the logs and so rule out the simple recovery model. If using SAN replication then simple is the way to go. 

    As usual - it depends ......

    Have fun

    Graham 
    Tuesday, June 2, 2009 6:46 AM
    Moderator
  • Continuing on, how about Index Rebuilds and Statistics updates?    Do we need to schedule these in on the weekends to maintain DB performance?
    Tuesday, June 2, 2009 3:41 PM
  • Yeah, I'm wondering about the index rebuilds and such also. I remember in one of the classes that the app takes care of creating and dropping it's own partitions, so maybe it does it's own reindexing as well? And I see that SQL Server only supports table partitions in Enterprise Edition. So I'm assuming that SCOM does something like create multiple tables and then puts a view on top of them? Is SCOM headed towards using Enterprise Edition for table partitioning? If so, will we need to expect a SKU upgrade in a future release?
    Tuesday, June 2, 2009 4:54 PM
  • There are rules that run daily on the OpsDB to handle switching the partitions for Events / Performance, grooming out old data, Rebuild/ReIndexing, and updating statistics.  We look at things like index fragmentation to determine if we need to update the indexes or not.  You should not need to do any such maintenence for either the OpsMgr database or the OpsMgr Data warehouse.
    Michael Pearson
    OpsMgr Performance Test Team

    This posting is provided "AS IS" with no warranties, and confers no rights. Use of attachments are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Wednesday, June 3, 2009 1:09 AM
  • Hi Kevin

    I would actually take the opposite approach on the autogrow - always disabling it, though for the very same reason that you give for enabling. In my view if a customer cannot monitor it properly then I'd prefer OpsMgr to do it. And by enabling autogrow, OpsMgr won't monitor the size. I wouldn't see autogrow as an insurance policy ... unless people read the small print (which you do mention - limit autogrow and have big chunks of growth). The other problem with autogrow is that you cannot specify when (what time of day) to do the growth.

    The Recovery Model to choose depends on what sort of DR is in place (if there is any!!). Obviously log shipping and replication type strategies require the logs and so rule out the simple recovery model. If using SAN replication then simple is the way to go. 

    As usual - it depends ......

    Have fun

    Graham 

    Graham - you make good points.  I just want to clarify one statement.

    The OpsDB free space IS monitored - even if Auto-Grow is enabled.

    The SQL MP's do not monitor free space on databases with autogrow.  However - the OpsDB is special.  There is a specific monitor for this - targeted at "System Center Operational Database Watcher" class.  This monitor will alert on the OpsDB as a warning at 40% free space, and a critical at 20% free space.  This monitor is not like the SQL MP - it will trigger regardless of the autogrow setting.

    Therefore - you get the same level of monitoring for the opsDB whether you set autogrow enabled or no.  It often just comes down to the preference of the SQL team or SCOM team that is responsible for the OpsDB.  I agree with you - it is a bad idea to rely on autogrow.  But at the same time - anything that buys you some time, still alerts, and keeps an outage (potentially) from occurring is a good thing, in my mind.

    As to recovery model... again - good points.  I always default to SIMPLE.... unless the customer has a solid business case for needing an advanced technology... such as up-to-the-last-tlog backup restore capability, log shipping, DB mirroring, etc.... I have seen a lot of customers fill their TLOGs and causing an outage because they were not prepared to respond in the short amount of time they have to fix a TLOG backup/truncate job.... should it ever fail.  If the customer understands the requirements here - then by all means that is a solid capability.
    Wednesday, June 3, 2009 5:20 AM
    Moderator
  • Thanks for the clarification Kevin.
    Cheers
    Graham
    Wednesday, June 3, 2009 8:56 AM
    Moderator
  • Agree, 30 GB is a good starting size.

    The trick is if you take the calculation sheets pulished in 2007, when OpsMgr was launched, and you follow the rule DB size = (5 MB / day / agent) + 510 MB starting size, you get a very small DB.

    My experience shows that with ~90 agents (all of them running on server machines) the DB grows to approx. 20 GB, not including logs and spare free space. Grooming is set to 10 days.

    I have another implementation where we calculated with a disk size of 10 GB for ~30 agents, but a few days ago we run out of the 20 GB disk partition. In this case, 20 GB includes some spare space and logs too.
    mz
    Wednesday, June 3, 2009 2:56 PM
  • FYI..SP1 and R2 upgrade change the autogrow flag on the OM DB back to off after upgrade.  You will need to re-enable it.
    Rob Kuehfus | System Center Operations Manager | Setup and Deployment Program Manager
    Wednesday, June 3, 2009 7:54 PM
    Moderator
  • Can I revive this thread?
    My OpsMgr runs quite slowly. Yes, it runs on VMware (and I'm moving it soon to Physical), but it has allocated 24GB RAM and 2 processors and 100GB C: drive (50% used).
    The datawarehouse server has 4GB RAM and 1 processor, with 20GB C: drive, 100GB E: drive (SAN) (~50% used)

    When I look at Task Manager, the SQLservr.exe process is ALWAYS chewing up >50% cpu, often topping 85% on BOTH machines.
    My 2ndary MS bumbles along happily at >80% idle CPU.

    Now having read about tuning the Databases from various points-of-view I have decided that I really don't want any data beyond two weeks worth. (It's only when things "hit the fan" that people around here want any data and then it's only for the few days prior to the "incident").

    The opsmgr DB is at 20GB and seems to be filling up with an alert occurring:  "Operations Manager operational database percent free space is 16.02%. Ops DB Server Name: MYSCOMSVR Ops DB Name: OperationsManager".

    The DW DB is at 35GB and 11GB Log.
    198 Servers are being monitored via agents. Not exactly a huge number. We use SAN storage etc.

    So my questions are:
    What can I do to stop the SQLservr.exe constantly churning at so great a rate and making everything go as slow as a man after a bottle of Johnny Walker?

    Is there any problem if I change the MaxDataAgeDays settings for each of the SQL tables on the DW down to 14 instead of the 100's , 400's etc as per the table listed at http://ops-mgr.spaces.live.com/blog/cns!3D3B8489FCAA9B51!176.entry or does that really not matter anyway?

    How can I free up space in the OpsMgr DB so that the "16% free" message happily goes away??

    Thx as always,
    John Bradshaw







    Monday, July 13, 2009 11:34 PM
  • Hi bradje,

    Did you install this hotfix?

    http://support.microsoft.com/kb/956240
    mz
    Tuesday, July 14, 2009 7:20 AM
  • Hi John

    A  few things around the configuration rather than the maintenance.

    OpsMgr Server:
    What other virtual machines are on the VMWare host? You might have allocated certain resources for OpsMgr but is it really getting them or is there a lot of contention for resource on the host?

    - When you say OpsMgr has 24 GB of RAM is that the RMS and Database? Where are the database files? All on the c:\ drive? Or is the database with the datawarehouse? SQL Server configuration should span multiple physical controllers - a system drive for windows and ideally separate physical controllers for a drive for the data file, a drive for the log file and a drive for tempdb. This is to keep contention down (the log file is a sequential file while data access is more random so should be separate). What sort of RAID are you using on the OpsMgr server? This is a good top 10 guide to storage:
    http://technet.microsoft.com/en-gb/library/cc966534.aspx

    - is your OpsMgr databases set to autogrow? If not, you can manually grow the database. SQL doesn't reclaim white space so it might be possible that there is some free space to reclaim (DBCC Shrink DB) but I wouldn't advise this unless you are with a DBA who can talk you through all the options \ possibile consequences.

    For the OpsMgr datawarehouse
    - again, are the logs \ data \ tempdb all on the same LUN? What RAID? Best practice is RAID 0+1 for data and to mirror the transaction logs. The actual size of your data warehouse is small so unless you are absolutely sure you don't need long term data, I'd probably advise not changing the values in case someone later asks for availability reports for the last quarter etc .... and then you have to tell them you don't have the data. 

    - 4 GB of RAM for the SQL data warehouse is ... errmm ... not a lot ... my laptop has that much (though I guess it neeeds it for vista!). I'd double that.

    If you have a DBA to hand then I'd take them for a drink ...  otherwise feel free to fly me to Sydney for a few days ... even if it is winter down there ;-)

    Cheers

    Graham



    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    • Proposed as answer by bradje Tuesday, July 14, 2009 9:02 PM
    Tuesday, July 14, 2009 7:35 AM
    Moderator
  • Thx, yes that Hotfix was installed a while back.
    JB
    Tuesday, July 14, 2009 8:22 PM
  • Agree, 30 GB is a good starting size.

    The trick is if you take the calculation sheets pulished in 2007, when OpsMgr was launched, and you follow the rule DB size = (5 MB / day / agent) + 510 MB starting size, you get a very small DB.

    My experience shows that with ~90 agents (all of them running on server machines) the DB grows to approx. 20 GB, not including logs and spare free space. Grooming is set to 10 days.

    I have another implementation where we calculated with a disk size of 10 GB for ~30 agents, but a few days ago we run out of the 20 GB disk partition. In this case, 20 GB includes some spare space and logs too.
    mz
    We have just over 600 agents installed, on our way to about 1,800-2,000 - and just today I got the alert that the OpsDB has less than 40% free.  Oddly enough, despite what Kevin mentioned about warnings and critical alerts, this came in as critical and it looks like it should have been a warning, (critical when less than 20%).

    At any rate, our OpsDB is around 40 GB with 7 day retention, and we don't have a huge variety of MPs installed, just Windows Server OS, DNS, IIS, SQL, Exch 07, Citrix, AD, Cluster, and Term Server.  Looks like I need to increase the size of the database already - I was not expecting to have to do this so soon.
    Tuesday, July 14, 2009 8:40 PM
  • Thx very much Graham.

    <<<<What other virtual machines are on the VMWare host? <<<< Several hundred others.......Let's just say that sometimes the cowboys get the upper hand, they mean well, but.....That is why I want a Physical box. It seems way too crowded for me, but no one else seems to complain much. I'm told we have about 700 servers spread over 6 Hosts.

    <<<When you say OpsMgr has 24 GB of RAM is that the RMS and Database? Where are the database files? All on the c:\ drive?<<<<< Yes to all. The RMS was originally built just on one virtual disk (C:) including the OpsMgr database.

    <<<<again, are the logs \ data \ tempdb all on the same LUN? What RAID?>>>> The DW is on a separate box. (Virtual C:, D: (apps), and E: drives. The Database and log file sit on the E: drive. All on the same LUN).

    <<<<4 GB of RAM for the SQL data warehouse is ... errmm ... not a lot >>>> Shall get the RAM doubled. That should be easy...

    <<<<even if it is winter down there ;-) >>> Remember winter down here = National Holiday in most of northern Europe!! It got down to 14C here the other day and was sooooo cold!! :) Needed an English cup of tea to warm me up..... after I had my Pineapple juice......

    So with the above it's probably best to wait for the Physical server to be installed.....Should be within the next month.
    I will also get the DBA's to help me set the OpsMgr DB to Autogrow, although I thought that it shouldn't need such an adjustment. Is there not a way to prune out the older data so that SQL regains it's space safely??

    Shall enjoy the read of the storage paper, although it will be interrupting "The Fabric of the Cosmos" right now, which is enthralling!!
    Cheers,
    John Bradshaw
    Tuesday, July 14, 2009 9:01 PM
  • "I will also get the DBA's to help me set the OpsMgr DB to Autogrow, although I thought that it shouldn't need such an adjustment. Is there not a way to prune out the older data so that SQL regains it's space safely??"

    I have to admit that I'm not a great fan of autogrow - the default settings tend to be quite small which results in a lot of fragmentation obver time. If you do turn this on then Kevin has some wise words further up the thread - "I will enable autogrow - but only as an insurance policy of the customer not monitoring and taking action when/if the DB fills up for some reason.  If I do enable autogrow - I ALWAYS limit autogrowth to 80% of the DB volume size, and I grow in set amounts of GB, not %."

    For pruning out the data - as you know you can change the amount of time that data is stored in the database so that data will be groomed from the database. But SQL doesn't shrink back as the data is deleted (there is an auto shrink option but it shouldn't be used!). If you do ever need to reclaim the space then DBCC ShrinkDB and DBCC ShrinkFile are 2 SQL commands that can be used. 

    Given the number of virtual machines on the host and the disk configuration there isn't much you can do ....... additionally, SQL is a resource hog in its own right and you might want to consider restricting the amount of memory it can use so that it doesn't starve the RMS of memory. If you can get a physical server within a month then probably best to spend the time planning the configuration - disk configuration is key here. Michael Pearson from MSFT made some good comments on another thread:

    As far as the SAN goes, SAN's are not always faster.  They can be slower than direct attached storage in some cases.  The performance is going to come down to how many physical disks are dedicated to OpsMgr, what the RAID configuration is, and how how SAN Cache is allocated to those sets of disks. I've worked with several customers that had a "big SAN with lots of disks" that turned out to be poorly configured for the type of IO that OpsMgr does.

    Two pieces of advice for SAN's
    1.  It's OK to waste space - Many people like to have one large RAID 5 Array in their SAN and carve out LUN's from that to allocate to the different servers in their environment.  That's a great solution to use every bit of disk space you have, but terrible for performance since every disk is basically shared.  Shared disks for SQL IO is generally a very bad idea. Every time I've worked with a customer that did this, they had performance problems (too many DB's on one disk, a single DB on one disk that was shared with dozens of other servers, etc).

    2.  RAID 5 is not the solution to every problem - While RAID 5 has it's benefits, our document recommends RAID 0/1  (aka RAID 10).  If you must go RAID 5, keep in mind that you may need more disks / larger write caches to come up to the performance that we tested with.

    also:
    http://blogs.technet.com/momteam/archive/2008/04/10/opsmgr-2007-hardware-guidance-what-hardware-do-i-buy.aspx

    Closest I'll get to Oz at the moment is a glass of Merlot in the hotel bar downstairs - the joys of Cardiff this week ;-)

    Have fun

    Graham
      
    View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/
    Tuesday, July 14, 2009 9:18 PM
    Moderator
  • Thx Graham,
    That gives me some homework to do....
    JB
    Tuesday, July 14, 2009 9:53 PM
  • Hi,

    Alert: Ops DB Free Space Low

    Source: xxxxxxxxx.com

    Path: xxxxxxx.com

    Last modified by: System

    Last modified time: 4/6/2012 8:45:45 PM

    Alert description: Operations Manager operational database percent free space is 19.94%. Ops DB Server Name: SQLXXXXXX Ops DB Name: OperationsManager

    The above is the alert i got and when i went on to see the properties of the Ops DB, it was set to "auto grow" ( i saw 1MB and auto grow, but not sure what that 1MB is) and i have around 7GB of free space out of 15GB on the drive where Ops DB is. Its coming from SAN.

    any idea on why i'm receiving this alert though the DB is set to auto grow?

    Thanks,

    Siva


    • Edited by Siva Darsi Friday, April 20, 2012 2:42 PM
    Friday, April 20, 2012 2:40 PM
  • Hi Siva,

    Not sure if you missed out reading Kevin's comment over here

    The OpsDB free space IS monitored - even if Auto-Grow is enabled.

    The SQL MP's do not monitor free space on databases with autogrow.  However - the OpsDB is special.  There is a specific monitor for this - targeted at "System Center Operational Database Watcher" class.  This monitor will alert on the OpsDB as a warning at 40% free space, and a critical at 20% free space.  This monitor is not like the SQL MP - it will trigger regardless of the autogrow setting.

    Therefore - you get the same level of monitoring for the opsDB whether you set autogrow enabled or no.  It often just comes down to the preference of the SQL team or SCOM team that is responsible for the OpsDB.  I agree with you - it is a bad idea to rely on autogrow.  But at the same time - anything that buys you some time, still alerts, and keeps an outage (potentially) from occurring is a good thing, in my mind.

    HTH,

    Varun

    Saturday, April 21, 2012 2:22 PM
  • Hi Varun,

    Thanks for your information. In my case, though i was alerted at 19.94% i shall be safe beacuse of the 7GB free space i have out of 15GB on the OPs DB drive? Please clarify.

    Thanks,

    Siva

    Monday, April 23, 2012 11:37 AM
  • Hi Kevin

    I would actually take the opposite approach on the autogrow - always disabling it, though for the very same reason that you give for enabling. In my view if a customer cannot monitor it properly then I'd prefer OpsMgr to do it. And by enabling autogrow, OpsMgr won't monitor the size. I wouldn't see autogrow as an insurance policy ... unless people read the small print (which you do mention - limit autogrow and have big chunks of growth). The other problem with autogrow is that you cannot specify when (what time of day) to do the growth.

    The Recovery Model to choose depends on what sort of DR is in place (if there is any!!). Obviously log shipping and replication type strategies require the logs and so rule out the simple recovery model. If using SAN replication then simple is the way to go. 

    As usual - it depends ......

    Have fun

    Graham 

    Does that count for the OpsMgrDW as well Graham?

    Regards,
    Chris

    Thursday, July 25, 2013 2:41 PM