locked
SQL MP - DB File Group Space Monitor Not Working RRS feed

  • Question

  • According to the SQL MP Guide (version 6.1.314.36) the DB File Space monitoring takes into account the amount of space left in the file plus the amount of space left on the drive (assuming autogrow is turned on).  We are seeing a problem where this isn't actually happening.  Our DB File Space counters don't appear to take the available disk space on the drive into account at all, even though we have verified that autogrow with unrestricted growth is enabled for that file.  The disk itself shows over 35% disk space free, and the file has about 12% free, but opsmgr puts it into a warning state and fires an alert, showing only 12% free.  It appears to be skipping the disk free calculations.  I verified we are up to date on our management packs and hotfixes (OpsMgr 2007 SP1 + SP1 Rollup + Cumulative).  The affected SQL Servers are Windows 2003 Ent. R2 SP2 64 bit, SQL 2005 9.0.4237.

    Anyone have any ideas?  Here is the documentation for the DB Free Space in the SQL Mgmt. Pack Guide:

    DB File Space

    Monitors the space available in a file and on the media hosting the file. The space available on the media hosting the files is only included as part of the space available if auto grow is enabled for this file.

    Every 900 (Interval) seconds, the monitor calculates the space available on the log files in percentages. If the value is less than the Lower Threshold (%), the monitor changes state to Critical. If value is less than the Lower Threshold (%) and greater than the Upper Threshold (%), the monitor changes state to Warning. Otherwise, the monitor saves the Healthy state.

    • Edited by Jerry Rice Friday, September 17, 2010 7:04 PM
    Wednesday, September 15, 2010 10:49 PM

Answers

  • no, just disable this monitor for this db (or for all db's with similar setup). Other db's will be monitored with the new features. As the deprecated monitor did not monitor filegroup space when autogrow was enabled it isn't worse as before. You might want to reconsider your threshold for the mountpoint disks in the os mp though as that will be the only alert you get about not enough space free.
    Rob Korving
    http://jama00.wordpress.com/
    • Marked as answer by Jerry Rice Tuesday, September 21, 2010 4:29 PM
    Tuesday, September 21, 2010 8:09 AM

All replies

  • Did you successfully import the updated SQL MP? (you said that, but double check).

    Have you tried to see if a SQL 2008 DB has the new behavior?  I don't believe all of the old MP files were updated for all scenarios in this last update.  I can check on that.


    Microsoft Corporation
    Thursday, September 16, 2010 3:39 PM
  • Yes, double-checked, the newest SQL MP is installed and looks fine.  Every other part of it's monitoring seems to be working correctly.

    We don't have any SQL 2008 DBs in that environment to try, so I have no way to test that.  There is another part of the story I was just reminded of, which is that the SQL Server disks are mount points.  The OS and SQL have no issues using them, and determining the disk space from within the OS works as expected, so I'm not sure that's an issue, but it's an important point.

    Since the other file space monitoring was deprecated, it seems like we do need this to work correctly.

    Thursday, September 16, 2010 5:27 PM
  • Yes, double-checked, the newest SQL MP is installed and looks fine.  Every other part of it's monitoring seems to be working correctly.

    We don't have any SQL 2008 DBs in that environment to try, so I have no way to test that.  There is another part of the story I was just reminded of, which is that the SQL Server disks are mount points.  The OS and SQL have no issues using them, and determining the disk space from within the OS works as expected, so I'm not sure that's an issue, but it's an important point.

    Since the other file space monitoring was deprecated, it seems like we do need this to work correctly.


    Before you didn't have file space monitoring when you had autogrow enabled, it just skipped space checking.

    Without having looked into it i assume this will be calculated by a script. See if you can find the script in the scom agent install path of the sql server. The diskspace free will probably be determined by a wmi query on logical disk, which might be different from what you see in the console. i wouldn't be surprised the script checks the logical drive (letter) which hosts the mountpoints instead of checking the free space on the mountpoint disk.

    The only way to know is to check the script and if my theory is correct i would just disable the script for this db (or all db's with autogrow set and hosted on mountpoints) and have the same monitoring as before the SQL update when it comes to file group space.


    Rob Korving
    http://jama00.wordpress.com/
    • Proposed as answer by Vivian Xing Monday, September 20, 2010 5:42 AM
    Saturday, September 18, 2010 7:49 AM
  • In looking at the GetSQL2005DBFileGroupFreeSpace.vbs script it is a bit hard to decode whether they are using what the OS considers to be the logical disk or whether they are using the mount point.  So, assuming it is, and this management pack won't work correctly with Mountpoints, your suggestion would be to go back to the deprecated monitors?

    Monday, September 20, 2010 10:01 PM
  • no, just disable this monitor for this db (or for all db's with similar setup). Other db's will be monitored with the new features. As the deprecated monitor did not monitor filegroup space when autogrow was enabled it isn't worse as before. You might want to reconsider your threshold for the mountpoint disks in the os mp though as that will be the only alert you get about not enough space free.
    Rob Korving
    http://jama00.wordpress.com/
    • Marked as answer by Jerry Rice Tuesday, September 21, 2010 4:29 PM
    Tuesday, September 21, 2010 8:09 AM
  • We don't have any DBs that are not on mount points or set to auto-grow, so we'll just disable the monitor entirely.  It's an answer, not the one we wanted, but an answer none-the-less.

    Does anyone know where we can request features for the SQL MP?  This monitor would have been great if it worked with mount points.

    Tuesday, September 21, 2010 4:58 PM
  • Anyone know if the SQL Server 2008 (Monitoring) MP 6.4.1.0 works with mount points?  I am seeing a lot of state changes in my environment with respect to disk and am thinking this might be the cause.
    Wednesday, June 4, 2014 7:23 PM
  • Scott,

    6.4.1.0 supports mount points.

    Wednesday, June 4, 2014 9:56 PM