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

  • Question

  • Hi, I have a very similar issue to the one reported in the following link but for sql 2008:http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/1d4fa1b6-b98b-4183-a14a-d49ba3b767c3?prof=required

    I am using management pack "SQL Server 2008 (Monitoring)", version 6.1.314.3.

    I'm getting the following alert message: "The file group "DATA"  for the database "xxxx" in SQL instance "inst1" on computer "xxxxx" is running out of space. See knowledge section for more details."

    I was able to locate the monitor generating this alert:

    Target: SQL 2008 DB File Group
    Monitor: DB File Space (rollup)
    Parent Monitor: DB File Group Space

    The database in question is not set to auto-shrink, and the filegroup has 30% free space. The drive in which the file is located is a mount volume and has 50% free space.

    It seems we shouldn't be getting this alert and yet we do.

    Can someone help me get to the bottom of this?

    Thank you,

    Marios Philippopoulos

    Wednesday, February 9, 2011 8:29 PM

Answers

  • Hi Vinay,

    We are also having this same issue with the DB space monitoring on mountpoints.  We are using the SQL 2008 MP ver.6.1.314.36.  Our SQL environment is SQL Server 2008 R2 CU2 on a Windows Server 2008 R2 cluster.  The DB is, according to one of our architects, is on NetApp virtualized disk with EVA spindles behind it.

    I exported out the SQL MP to an XML file to see if something stood out and had one of our DBAs take a look.  These are his comments:

    "However it’s not really a ‘bug’ with anything – it IS an issue with the way the script determines the disk space. It queries the SQL instance, and returns the only the first character of the ‘physical_name’ field for the datafile (see script quote below) and therefore cannot take mount points into account.

    …SUBSTRING(physical_name,1,1) as drive…

     

    The only fix here is to fix the current script to check for the existence of mount points (maybe using the win32_volume WMI object which can also return the free disk space?)."

    Thanks,

    Ron

    Wednesday, February 23, 2011 1:18 PM
  • After investigation from our team, there might be a bug in the DB space monitoring script for dbs located on mount points. We are not able to reproduce the problem and will need more inputs from the customer about their environment configuration. Do we have a repro we can take a look at?

    -vinay

    Senior Program Manger, SQL MP

    • Marked as answer by Nicholas Li Monday, February 28, 2011 1:54 AM
    Tuesday, February 15, 2011 9:52 PM
  • Marios,

    It appears to us to be an issue with the information that is used to calculate free space.  We don't have any issues with this MP on databases on local disk, just the ones using mount points.  If I understand how it was explained to me, the script in the MP is not looking at the complete path to the DB but only the root of the mount point.

     

    Ron

    Friday, March 4, 2011 8:32 PM
  •  

    Hi Marios,

     

    Regarding this issue, please try the method in the following document:

     

    Appendix: Known Issues and Troubleshooting

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

    Please see the section “Data file and log file free space and free space percent collection rules and monitors might return inaccurate size values”.

     

    Meanwhile, I noticed a thread which seems to be about a similar issue:

     

    SQL MP - DB File Group Space Monitor Not Working

    http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/1d4fa1b6-b98b-4183-a14a-d49ba3b767c3/

     

    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.
    • Marked as answer by Nicholas Li Monday, February 28, 2011 1:54 AM
    Monday, February 14, 2011 7:59 AM

All replies

  • Have you looked at the knowledge for this?  Have you tried adjusting the thresholds?
    Microsoft Corporation
    Wednesday, February 9, 2011 9:11 PM
  • Have you looked at the knowledge for this?  Have you tried adjusting the thresholds?
    Microsoft Corporation

    I haven't been able to locate any thresholds in the monitor, and no thresholds are exposed in the overrides section, except for something called "percentage" which is set by default to 0.
    Wednesday, February 9, 2011 9:14 PM
  •  

    Hi Marios,

     

    Regarding this issue, please try the method in the following document:

     

    Appendix: Known Issues and Troubleshooting

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

    Please see the section “Data file and log file free space and free space percent collection rules and monitors might return inaccurate size values”.

     

    Meanwhile, I noticed a thread which seems to be about a similar issue:

     

    SQL MP - DB File Group Space Monitor Not Working

    http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/1d4fa1b6-b98b-4183-a14a-d49ba3b767c3/

     

    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.
    • Marked as answer by Nicholas Li Monday, February 28, 2011 1:54 AM
    Monday, February 14, 2011 7:59 AM
  • After investigation from our team, there might be a bug in the DB space monitoring script for dbs located on mount points. We are not able to reproduce the problem and will need more inputs from the customer about their environment configuration. Do we have a repro we can take a look at?

    -vinay

    Senior Program Manger, SQL MP

    • Marked as answer by Nicholas Li Monday, February 28, 2011 1:54 AM
    Tuesday, February 15, 2011 9:52 PM
  • Hi Vinay,

    We are also having this same issue with the DB space monitoring on mountpoints.  We are using the SQL 2008 MP ver.6.1.314.36.  Our SQL environment is SQL Server 2008 R2 CU2 on a Windows Server 2008 R2 cluster.  The DB is, according to one of our architects, is on NetApp virtualized disk with EVA spindles behind it.

    I exported out the SQL MP to an XML file to see if something stood out and had one of our DBAs take a look.  These are his comments:

    "However it’s not really a ‘bug’ with anything – it IS an issue with the way the script determines the disk space. It queries the SQL instance, and returns the only the first character of the ‘physical_name’ field for the datafile (see script quote below) and therefore cannot take mount points into account.

    …SUBSTRING(physical_name,1,1) as drive…

     

    The only fix here is to fix the current script to check for the existence of mount points (maybe using the win32_volume WMI object which can also return the free disk space?)."

    Thanks,

    Ron

    Wednesday, February 23, 2011 1:18 PM
  • Thank you all for your responses.

    Ron's configuration is very similar to mine: Windows cluster with mount points, SQL 2008 SP1/SP2 on Windows Server 2008 R2.

    So it seems there is something wrong with the scripts?

    Monday, February 28, 2011 4:47 PM
  •  

    Hi Marios,

     

    Regarding this issue, please try the method in the following document:

     

    Appendix: Known Issues and Troubleshooting

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

    Please see the section “Data file and log file free space and free space percent collection rules and monitors might return inaccurate size values”.

     

    Meanwhile, I noticed a thread which seems to be about a similar issue:

     

    SQL MP - DB File Group Space Monitor Not Working

    http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/1d4fa1b6-b98b-4183-a14a-d49ba3b767c3/

     

    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.


    Hi,

    Thank you for the reply, but this link does not apply in my case, as we have not had our databases updated from SQL 2000.

    Appendix: Known Issues and Troubleshooting

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

    Regards,

    Marios

    Monday, February 28, 2011 4:53 PM
  • Marios,

    It appears to us to be an issue with the information that is used to calculate free space.  We don't have any issues with this MP on databases on local disk, just the ones using mount points.  If I understand how it was explained to me, the script in the MP is not looking at the complete path to the DB but only the root of the mount point.

     

    Ron

    Friday, March 4, 2011 8:32 PM
  • Marios,

    It appears to us to be an issue with the information that is used to calculate free space.  We don't have any issues with this MP on databases on local disk, just the ones using mount points.  If I understand how it was explained to me, the script in the MP is not looking at the complete path to the DB but only the root of the mount point.

     

    Ron


    Thank you, I think that settles it for now. I guess a subsequent version of the management packs will address that?
    Monday, March 7, 2011 4:03 PM
  • Vinay,

    Can you give us an update if a hotfix is in the works for this issue?  I am being pressured to get our issue resolved.

    Thanks,

    Ron

    Wednesday, March 30, 2011 12:08 PM
  • This too big of a change for a hotfix. Having said that, given the importance of the issue we will priotize this issue higher for in the major version of SQL server (SQL 11) management pack.
    Wednesday, March 30, 2011 7:01 PM
  • OK,  thank you very much for the update.

     

    Ron

    Wednesday, March 30, 2011 8:11 PM
  • Hello!

    We have also this problem. Is it fixed in the newest version (6.1.400.0) of Management Pack (MP) or?

    Also another question: How can I determine which version of Management Pack do I have? Is MP automatically included in SQL Server 2008 Enterprise?

     

    Thanks,

    Tommi

    Thursday, July 14, 2011 12:39 PM
  • The next version of SQL management pack will support DB space monitoring for dbs located on mount points. The management pack will be released along with the sql server "denali" release.
    Friday, July 15, 2011 12:00 AM
  • Thanks for reply. Any idea when the next version will be released?

    Can I still have answers for these questions: How can I determine which version of Management Pack do I have? Is MP automatically included in SQL Server 2008 Enterprise?

    Friday, July 15, 2011 5:55 AM
  • Hi i have this trouble with sccom 2012 . some solution ?

    Falcon

    Saturday, May 26, 2012 10:02 AM
  • Hi I have this trouble in the SCOM 2012 . SOme solution ?

    Falcon

    Saturday, May 26, 2012 12:52 PM
  • Hi

    I've found SCOM to be sensitive to lower case drive letters used in the database file paths. This has not been exclusive to Lync. It seems that Microsoft have failed to notice their Wizards causing problems by automatically using lower case letters when creating the databases!

    Query the offending database, in this case ‘tempdb’ with the following;

    select* from sysfiles

    Check the results for any lower case drive letters.

    Now modify and run this ALTER DATABASE command against each log/database file to replace the lower case drive letter.

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, FILENAME= ‘C:\**\tempdb.mdf’)

    *Note the upper case 'C:\'

    Confirm the update by running the following ommand on the offending database.

    select* from sysfiles

    I hope this helps.

    Please let me know how you get on.

    Many thanks

    • Proposed as answer by HelpDaz Friday, October 19, 2012 2:44 PM
    Friday, October 19, 2012 2:44 PM