none
SQL Server 2005 6.3.173.0 returning 0 for DB File Group Free Space

    Question

  • Upgraded to mp 6.3.173.0 and now getting an alert on my sql 2005 machine that my filegroup PRIMARY for tempdb is running out of space. There's 50Gig free on the drive and tempdb is set to autogrow. This error only occurred after upgrading the management pack.

    The performance data has been showing 0 since the mp upgrade.

    Thanks.

    Wednesday, April 11, 2012 5:05 PM

Answers

  • Hi,

    I investigated this today. Took me about 4 hours to find the problem.

    They again made a mistake with the filepath.

    In 6.1.400.0 there was a problem with mount points and taking only the first letter of the path as volume.

    In 6.3.173.0 they forgot that "Instr" in VBScript is case sensitive!

    Check your filepath if the drive letter is a small letter and not a Capital.

    I executed the follow query for TempDB

    ALTER Database tempdb Modify file (name = 'tempdev', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\tempdb.mdf')

    Hope this helps for you too.

    Regards,

    Peter

     

    • Marked as answer by Rick Dorris Thursday, April 12, 2012 6:09 PM
    Thursday, April 12, 2012 2:30 PM

All replies

  • I too noticed this. Flooded with space alerts and all of the reports and datat show 0
    Thursday, April 12, 2012 12:12 AM
  • Thirded, have two databases currently giving me this out of about 20 on one SQL Server. Not quite sure how this value is getting calculated as yet.

    Thursday, April 12, 2012 1:30 PM
  • Hi,

    I investigated this today. Took me about 4 hours to find the problem.

    They again made a mistake with the filepath.

    In 6.1.400.0 there was a problem with mount points and taking only the first letter of the path as volume.

    In 6.3.173.0 they forgot that "Instr" in VBScript is case sensitive!

    Check your filepath if the drive letter is a small letter and not a Capital.

    I executed the follow query for TempDB

    ALTER Database tempdb Modify file (name = 'tempdev', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\tempdb.mdf')

    Hope this helps for you too.

    Regards,

    Peter

     

    • Marked as answer by Rick Dorris Thursday, April 12, 2012 6:09 PM
    Thursday, April 12, 2012 2:30 PM
  • Hi!

    This is not working as expected. Find the following information in the MP guide:

    Many databases on the same drive

    Default space monitoring settings are noisy in environments where data or log files for many databases are located on the same drive and have autogrow “On”. In such environments, an alert for each database is generated when the amount of free space on the hard drive reaches the threshold. To avoid noise, turn off the space monitors for data and log files, and use Base OS Management Pack to monitor space on the hard drive.

    Cheers,

    Patrick

    Thursday, April 12, 2012 2:41 PM
  • I am not a SQL dude so can you convert this comment into non-SQL speak.

    My filepath for my db is 'e:\folder\dbname.mdf'

    Are you saying that I need to change it to 'E:\folder\dbname.mdf' ?

    Thursday, April 19, 2012 10:05 PM
  • Yes, that has been the fix for me. Run the ALTER database command as shown above using your appropriate name and filename. Run it for both the data file and log file.
    Thursday, April 19, 2012 10:28 PM
  • In 6.3.173.0 they forgot that "Instr" in VBScript is case sensitive!

     

    Has this been submitted as a bug? I'm in the planning stages of updating to the 6.3.173.0 versions of the SQL MP's.
    Thursday, April 19, 2012 10:35 PM
  • 2012 and people still using case sensitive commands.

    You'd have thought MS would have converted all the VB into powershell by now aswell.

    SC2012 is a great suite of tools but the underlying tech (MP's etc) are rough and show MS development is behind the curve even with their own tools.

    Thursday, April 19, 2012 10:56 PM
  • @Gary Hay: So your recommendation is that MS should rewrite Instr so that it's not case sensitive? Yeah. Ok.
    Thursday, April 19, 2012 11:00 PM
  • No my recommendation is to stop using VB and move the underlying checks to powershell.

    Thursday, April 19, 2012 11:02 PM
  • Use of the UCASE() vb command to convert the string to uppercase before using the Instr() command should do the trick.

    I would log this as a bug via the usual Microsoft Connect site, but is it just me or has the Operations Manager 2007 R2 program disappeared from the Connect Directory?  There is a generic System Center program but this is for SC 2012 Evaluation.

    The other thing that annoys me is that they've got this obviously buggy MP out there, but rather then get a quick fix out there and save everyone a bunch of time troubleshooting, they'll wait 9-11 months before the next release. Indeed the last two releases of the SQL MP pack were in May 2011 and August 2010.

     

    • Proposed as answer by Etchavious Friday, May 11, 2012 4:27 PM
    • Unproposed as answer by Etchavious Friday, May 11, 2012 4:27 PM
    • Proposed as answer by Etchavious Friday, May 11, 2012 4:47 PM
    • Unproposed as answer by Etchavious Friday, May 11, 2012 5:12 PM
    Monday, April 23, 2012 9:31 AM
  • For me the solution was different.

    allthough the tempdb had enough space, the tempdb has some grows compared to the "initial size". i manually set the initial size to a value higher then the current tempdb size and log. After that the alerts disappeared.

    initial size 8 mb

    actual size 129.xx mb used ~35mb

    set "initial" size to 200mb and the alerts autocleared.

    So it seems the script doesn't check for enough space, but alerts when the current size is larger than (a percentage of) the initial size. (all done without actually looking at the script :))


    Rob Korving
    http://jama00.wordpress.com/


    • Edited by rob1974 Thursday, May 10, 2012 2:11 PM
    Thursday, May 10, 2012 2:04 PM
  • Peter/Steve,

    You guys hit this nail right on the head.  I was able to take what you said and reproduce the issue.  I'm not entirely sure when MS is going to release a patch, so I took matters into my own hands and modified the SQL 2005 and 2008 (Monitoring) Management Packs.  Below are the steps I took to make this possible.


    Please use the following instructions at your own risk.  The below instructions are just an outline of the procedures I took in order to resolve the issue, and while hypothetically should result the same for you, may cause some time of world-ending time-space collapse.  While I highly doubt this will occur, please keep in mind that this is not an official Microsoft response or official fix.


    I'm relatively new to SCOM 2012, so I had to do some digging on how the MPs work and how everything connects in.  I was able to find a blog on how to export the management packs to a XML file.  According to my tests, it is possible to modify these XML files then re-import them as unsealed (and unsupported) management packs.  So let's do that!

    1. Export your Management Packs on your SCOM Management Server (http://mpalchemy.wordpress.com/2011/11/20/exporting-sealed-om2012-management-packs-to-xml/)
    2. The Instr() functions that you need to tweak are on the following lines.
      • SQL 2005 Monitoring XML File: 886, 1874, 2864
      • SQL 2008 Monitoring XML File: 1125, 2113, 3103
      • If those line numbers don't work for you, do a find for the string of: "instr(filepath"
      • Change "If InStr(filePath, DiskVolumesInfo.MountPoints.Item(mpKey).Caption) > 0 Then" to "If InStr(UCase(filePath), DiskVolumesInfo.MountPoints.Item(mpKey).Caption) > 0 Then" adding the UCase() function around the filepath variable.
    3. Optional: Tweak the Version to state custom work has been done.  I did this so I had an ascetic change on my management server.  I've tested that changing just the revision number has no affect on functionality.  Just don't do what I did and start changing the ID Names and whatnot :)
    4. Optional: Tweak the Name and Description so that your MP states custom work has been done from within your MP Management in SCOM
      • SQL 2005:Do a search for "<Name>SQL Server 2005 (Monitoring)</name>".  This will get you to the location you need to be.  I then appended "- Custom" to the name, so that when I import this MP in, in my SCOM Management Interface, it shows that it's been customized.
        Then! Right below is the description line.  You can choose to leave this alone, or add the word Custom in there right after "2005".  Once again, this is ascetic.
      • SQL 2008:
        Do a search for "<Name>SQL Server 2008 (Monitoring)</name>".  This will get you to the location you need to be.  I then appended "- Custom" to the name, so that when I import this MP in, in my SCOM Management Interface, it shows that it's been customized.
        Then! Right below is the description line.  You can choose to leave this alone, or add the word Custom in there right after "2008".  Once again, this is ascetic.

    Personally, I prefer to modify the Management Packs like this, over changing core configurations on my production SQL servers.  Granted, this is a temporary fix until MS releases and updated patch, but it works!

    I've done some testing to make sure that the Version change and the Name/Description change had no affect on previous alerts.  If I missed something somewhere, please reply and let me know so I can test further and/or fix my instructions.

    Just be careful, because now you're importing in an unsealed MP.  Before, when the MP was sealed, you were unable to save overrides to it, and you had to save it to either the default override MP or a custom one created for that specific MP.  Now that it's unsealed, it is possible to save overrides to it.  Don't do this, stay away from the Dark Side!  Keep in mind when MS fixes their management pack in the future and you'll have to delete it and add the new one, then you'll lose all your overrides that you saved to it!

    Friday, May 11, 2012 5:12 PM
  • Truly a silly mistake, but I'm wondering why is it still not fixed. It's not a big thing to change it/sign it.

    Has a microsoft representive more information about a release date?

    Thanks

    Tuesday, May 15, 2012 2:03 PM
  • That's not a good fix - and here is why.

    Unsealing an MP - modifying it, is almost never the right solution.  The better solution is to disable the workflows that have a bug via overrides, and recreate ONLY those workflows (fixing them) in your own MP.  This way you can simply upgrade the MP when Microsoft ships a fix, and remove your fix-MP.  By unsealing a tweaking our MP, you break upgrade capability.... and have to start over.  Most customers have too many overrides that your process will break, and cause them to have to recreate them all.

    I do wish we had a simpler way to handle minor fixes to scripts and such, but we are really dependent on the MP authors to issue a release quickly to resolve these types of problems.


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

    Thursday, May 31, 2012 1:53 PM
    Moderator
  • Kevin,

    I appreciate the feedback regarding how to properly handle the Management Pack.  I completely forgot about the link for overrides.  In an environment such as mine which was a new install, I was allowed to do this without any side-effects.  However, thinking about other customers and already having SQL overrides in place, yes, I totally understand that now.

    I will take a look into recreating the workflow for that specific monitor in a custom MP and disable the official one until and update is pushed out.  Thanks for the clarification, I apologize to anyone I have misled with this information.

    On a side note: Do you know if this has been submitted as a bug yet?  This seems like such a simple fix that shouldn't take a few months to fix and push out.  I don't see Operations Manager as an option at connect.microsoft.com so I'm unsure of how to submit this bug to the team.  Would you have any advice on how to accomplish this task?

    Thanks!
    Jason

    • Edited by Etchavious Tuesday, June 19, 2012 5:30 PM side note addition
    Tuesday, June 19, 2012 5:06 PM
  • Does running the command "ALTER Database tempdb Modify file (name = 'tempdev', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\tempdb.mdf')" cause an outage?

    I see from http://technet.microsoft.com/en-us/magazine/gg452698.aspx that it looks like the outage is caused when you combine that command with set offline and set online, but hopefully since nothing is really changing we can run the command above w/o taking the databases offline.

    And can you modify the file path using this method for databases other than the Tempdb? The following link tends to make me think the answer is no: http://msdn.microsoft.com/en-us/library/aa275464(v=sql.80).aspx

    Friday, June 29, 2012 5:10 PM
  • Hi all,

    You may want to check my 'hotfix' MP here: http://opsmgr.ru/Lists/Posts/Post.aspx?ID=312

    Just import it and delete when you'll get a new version of original SQL MP.

    HTH


    http://OpsMgr.ru/

    Monday, July 09, 2012 10:48 AM
    Moderator
  • Seriously, where is the fix for this??
    Thursday, July 26, 2012 6:00 PM
  • This worked great! I tried to override the alert to 99% disk usage for those machines, but when that didn't work came across this page.

    Thanks alot!

    Friday, August 10, 2012 5:59 PM
  • So what is the fix here? We have 3-4 SQL 2005 servers showing this in SCOM.  Do you run the ALTER command on each server throwing the alert or on the RMS or what?  I dont know that much about SCOM 2007
    Monday, August 20, 2012 2:31 PM
  • The fix for this issue is now available here

    http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=10631

    Sorry about the delay in releasing the fix for this issue. Thanks for your continued support and feedback for SQL MP

    -Vinay

    PM for SQL MP

    Tuesday, August 21, 2012 1:56 AM
  • 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


    • Edited by HelpDaz Friday, October 19, 2012 2:53 PM
    • Proposed as answer by HelpDaz Friday, October 19, 2012 2:53 PM
    Friday, October 19, 2012 2:45 PM
  • Hi there

    We are running version 6.3.173.1 and are getting a similar issue but our drive letters in the File path are definitely in capital letters.

    This is only effecting SQL Server 2012 servers though.

    Our exact issue is that the DB Total Space monitor is alerting "Database Out of Space" with a value of "0" for master, msdb and temp databases. None of the other databases are alerting?!

    We don't want to hack the MP and the above fix is N/A re already being configured with capital F:\ (as an example).

    Cheers

    Thursday, August 01, 2013 2:56 AM
  • Hi there

    We are running version 6.3.173.1 and are getting a similar issue but our drive letters in the File path are definitely in capital letters.

    This is only effecting SQL Server 2012 servers though.

    Our exact issue is that the DB Total Space monitor is alerting "Database Out of Space" with a value of "0" for master, msdb and temp databases. None of the other databases are alerting?!

    We don't want to hack the MP and the above fix is N/A re already being configured with capital F:\ (as an example).

    Cheers

    Fixed our issue. NTAUTHORITY\SYSTEM is usually a SYSADMIN in our builds (and our SQL MP relies on this). Because our DBA created a new build for SQL 2012, he overlooked this piece. Once we added back in, the alerts cleared and Performance data populated correctly (which I didn't realise was missing until after I posted the above).

    Cheers

    Thursday, August 08, 2013 12:53 AM
  • Great news! It looks like this is still a problem with 6.4.1.0!!! High fives all around!
    Wednesday, January 22, 2014 9:35 PM
  • Great news! It looks like this is still a problem with 6.4.1.0!!! High fives all around!

    Hi Blake,
    Yep, still have the same error with MP 6.5.4.0.  I did export the MP and looked at the XML.  Instead of If InStr(filePath, they have If InStr(Lcase(filePath).  DBAs confirmed they have used uppercase for drive letters in their file path.

    :-)

    Wednesday, June 17, 2015 2:52 PM