Post reply

[sys].[dm_os_volume_stats]

  • May 23, 2013 at 10:36 am

    #294328

    I'm working on collecting stats on my sql servers to better keep an eye on them, and there's a table that I'd like to have access to the information, however, when I run the script to do this I get an error:

    Script:

    SELECT DISTINCT

    ( [vs].[logical_volume_name] ) AS 'Drive' ,

    [vs].[available_bytes] / 1048576 AS 'MBFree'

    FROM [sys].[master_files] AS f

    CROSS APPLY [sys].[dm_os_volume_stats]([f].[database_id],

    [f].[file_id]) AS vs

    ORDER BY [vs].[logical_volume_name];

    Error:

    Invalid object name 'sys.dm_os_volume_stats'.

    This script works on some sql servers but not on others, I believe is the different version. I'd really like to run this on all my server. Is there a replacement on the RTM version that I can use?

    The version in which it works:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    The version in which it doesn't work:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1790.0 (X64) Apr 22 2011 11:55:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

    Thank you.

  • May 23, 2013 at 10:41 am

    #1617930

    if you run the command in the master database on the server that seems to fail, does it work?

    CROSS APPLY for a function will fail syntax if the database compatibility is 90/SQL2005, so

    since the master database would be 10, i think it might work in that case?

    never mind, it doesn't work for me on my local 2008, nor 2008R2; only for my 2012 instance.

    10.0.4000.0SP2Standard Edition (64-bit)

    10.50.1600.1RTMExpress Edition (64-bit)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 23, 2013 at 10:46 am

    #1617933

    I ran the script:

    SELECT DISTINCT

    ( [vs].[logical_volume_name] ) AS 'Drive' ,

    [vs].[available_bytes] / 1048576 AS 'MBFree'

    FROM master.[sys].[master_files] AS f

    CROSS APPLY master.[sys].[dm_os_volume_stats]([f].[database_id],

    [f].[file_id]) AS vs

    ORDER BY [vs].[logical_volume_name];

    It also gave me the error: Invalid object name 'master.sys.dm_os_volume_stats'.

  • May 23, 2013 at 10:47 am

    #1617935

    this link says the function is part of 2008R2 SP1 or later:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/250ee9a5-e851-4449-b279-165fe0ccd600/

    It is available in SQL 2008 R2 with SP1 or later. http://msdn.microsoft.com/en-us/library/hh223223(SQL.105).aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 23, 2013 at 10:53 am

    #1617939

    It doesn't give mount point information. May be you could use this: http://www.sqlservercentral.com/scripts/90392/

  • May 23, 2013 at 12:25 pm

    #1617985

    Lowell

    Thank you for the link, I'll take a look. The easy fix would be to apply the SP1 and bring the server to that level. The easy fix... Potentially introducing others ...

  • May 23, 2013 at 12:26 pm

    #1617988

    ErrLog,

    That's a good script that I'll make sure I use. Thank you for pointing it out.

  • June 3, 2013 at 12:13 am

    #1620393

    I use PowerShell for this kind of thing and not through T-SQL either, it's a lot more flexible that way. If you look into the script further you'll see some hellacious string parsing happening to split apart into columns what xp_cmdshell mangled from the PowerShell output. If you're an admin on the server where you want to get free disk space, which one would expect you to be if you can run xp_cmdshell so inspect the local volumes, then you can use the Get-WmiObject and supply the -ComputerName option to query the free disk space on any number of computers on your network. As long as the server is Server 2003 or newer then the Win32_Volume class will be available. You can dump the results to a csv and then bcp them into a SQL table. No nasty string parsing and if you're not already using xp_cmdshell there is no need to enable it just for this.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply