none
View, dm_os_volume_stats, missing after Service Pack 2 install

    Question

  • I installed Service Pack 2 for SQL Server 2008 R2 Standard Edition.  No errors were encountered.  However I expected to find a new view called, dm_os_volume_stats.  I do not see this view.  I have patched 4 instances and none of those instances have this view.  Please advise.
    Thursday, March 20, 2014 6:29 PM

Answers

  • Its not a view. Its a Dynamic management function. check in Table valued function of master database.

    Can you run the below query

    SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
    FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
    

    -Prashanth

    • Proposed as answer by Naomi N Thursday, March 20, 2014 7:59 PM
    • Marked as answer by Joe Hinch Thursday, March 20, 2014 8:20 PM
    Thursday, March 20, 2014 7:21 PM
  • Yes.  Now I see, dm_os_volume_stats, under 'System Functions'.  The query returned information.

    Thank you for the clarification.

    • Marked as answer by Joe Hinch Thursday, March 20, 2014 7:33 PM
    Thursday, March 20, 2014 7:33 PM

All replies

  • Hello Joe,

    Can you check the version with this SQL statement

    SELECT @@VERSION

    and post the result?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 20, 2014 6:37 PM
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    Thursday, March 20, 2014 6:39 PM
  • Have you verified the @@VERSION on all these servers? Do you have VIEW SERVER STATE permission?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 20, 2014 6:42 PM
  • I have verified the version as, Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

    I do have the VIEW SERVER STATE permission.

    Thursday, March 20, 2014 6:59 PM
  • Its not a view. Its a Dynamic management function. check in Table valued function of master database.

    Can you run the below query

    SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
    FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
    

    -Prashanth

    • Proposed as answer by Naomi N Thursday, March 20, 2014 7:59 PM
    • Marked as answer by Joe Hinch Thursday, March 20, 2014 8:20 PM
    Thursday, March 20, 2014 7:21 PM
  • Yes.  Now I see, dm_os_volume_stats, under 'System Functions'.  The query returned information.

    Thank you for the clarification.

    • Marked as answer by Joe Hinch Thursday, March 20, 2014 7:33 PM
    Thursday, March 20, 2014 7:33 PM
  • SELECT name, type, type_desc
    FROM sys.system_objects
    WHERE name LIKE '%dm_os_volume%'
    ORDER BY name

    You can also use above to find any such DMV's or DMF's

    -Prashanth

    • Proposed as answer by Naomi N Thursday, March 20, 2014 7:59 PM
    Thursday, March 20, 2014 7:34 PM