Get file sizes of database files and free space on disk

  • Jonathan AC Roberts

    SSCoach

    Points: 16886

    Comments posted to this topic are about the item Get file sizes of database files and free space on disk

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Well done. One word of warning. sys.xp_fixeddrives cannot read mounted volumes and accurately report the size. If you are running SQL 2008 R2 SP1 or above, take a look at sys.dm_os_volume_stats as a replacement.

    SELECT DISTINCT

    volume_mount_point

    , CAST( ( total_bytes / 1073741824. ) as numeric(18,4)) AS [Total_GB]

    , CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) AS [Available_GB]

    , CAST(

    ( CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) /

    CAST( ( total_bytes / 1073741824. ) as numeric(18,4))

    ) * 100.0000

    as numeric(18,4)) AS [PercentFree]

    FROM sys.master_files AS f CROSS APPLY

    sys.dm_os_volume_stats(f.database_id, f.FILE_ID)

    ORDER BY volume_mount_point;

    vs.

    EXECUTE sys.xp_fixeddrives;

  • Jonathan AC Roberts

    SSCoach

    Points: 16886

    Hi Christopher

    The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.

  • Perry Whittle

    SSC Guru

    Points: 233794

    Jonathan AC Roberts (7/24/2015)


    Hi Christopher

    The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.

    Xp_fixeddrives will not provide information for mounted volumes you need to account for this to make the size reports accurate in a pre sql 2008 environment.

    Mounted volumes were fully supported in 2000 and 2005 so your script may report space incorrectly

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Jonathan AC Roberts

    SSCoach

    Points: 16886

    Perry Whittle (8/14/2015)


    Jonathan AC Roberts (7/24/2015)


    Hi Christopher

    The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.

    Xp_fixeddrives will not provide information for mounted volumes you need to account for this to make the size reports accurate in a pre sql 2008 environment.

    Mounted volumes were fully supported in 2000 and 2005 so your script may report space incorrectly

    Yes, dm_os_volume_stats is only available in SQL Server 2008 and higher. I'm not sure how I can get the volume space information from versions less than 2008 so I used the Xp_fixeddrives to get the space on the drive. Fortunately in our environment we only use mounted volumes on SQL Server 2008 and higher all our SQL 2005 database just use drives.

    Have you any ideas on how to get volume information from pre SQL 2008 databases?

  • Lee Linares

    SSCrazy

    Points: 2664

    Jonathan,

    Great script. Thanks for sharing. One small problem though is this line:

    IF CONVERT(int, @ServerVersion) < 10

    The issue is that SQL Server 2008 does NOT support sys.dm_os_volume_stats which was not introduced until 2008 R2.

    If you execute the script on a SQL Server 2008 (Non R2) you will get this error:

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'sys.dm_os_volume_stats'.Msg 208, Level 16, State 1, Line 2

    Changing this line:SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)

    To this: SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion,4)-1)

    AND changing this line: IF CONVERT(int, @ServerVersion) < 10

    to this: IF @ServerVersion < 10.5 BEGIN --–2000, 2005

    SEEMS to work (I haven't completely finished testing).

    Thanks again.

    Lee

  • Jonathan AC Roberts

    SSCoach

    Points: 16886

    Lee Linares (8/17/2015)


    Jonathan,

    Great script. Thanks for sharing. One small problem though is this line:

    IF CONVERT(int, @ServerVersion) < 10

    The issue is that SQL Server 2008 does NOT support sys.dm_os_volume_stats which was not introduced until 2008 R2.

    If you execute the script on a SQL Server 2008 (Non R2) you will get this error:

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'sys.dm_os_volume_stats'.Msg 208, Level 16, State 1, Line 2

    Changing this line:SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)

    To this: SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion,4)-1)

    AND changing this line: IF CONVERT(int, @ServerVersion) < 10

    to this: IF @ServerVersion < 10.5 BEGIN --–2000, 2005

    SEEMS to work (I haven't completely finished testing).

    Thanks again.

    Lee

    Hi Lee,

    Thanks for that! I've made the changes you suggested to the script and submitted the updated version for republication.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the helpful script.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • DBAMike

    Mr or Mrs. 500

    Points: 515

    An excellent script and one I use a lot however having just inherited a system with a FILESTREAM database I'm wondering the best way to get this FILESTREAM type included in the results

Viewing 10 posts - 1 through 10 (of 10 total)

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