Get file sizes of database files and free space on disk

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

  • 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;

  • 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.

  • 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

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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?

  • 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

  • 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.

  • Thanks for the helpful script.

  • Thanks for the script.

  • 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

  • DECLARE @ServerVersion varchar(100)

    SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))

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

    --PRINT @ServerVersion

    DECLARE @command nvarchar(2000)

    IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL

    BEGIN

    PRINT 'Dropping #FileData'

    DROP TABLE tempdb..#FileData

    END

    CREATE TABLE tempdb..#FileData

    (

    [CurrentHost] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [ClusterNodes] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [DB] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [FileType] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [Name] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [VolumeOrDrive] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [FileName] varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [File Size (MB)] decimal(15,2) NULL,

    [Space Used In File (MB)] decimal(15,2) NULL,

    [Available Space In File (MB)] decimal(15,2) NULL,

    [Drive Free Space (MB)] decimal(15,2) NULL

    )

    IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008

    IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL

    BEGIN

    PRINT 'Dropping table #xp_fixeddrives'

    DROP TABLE #xp_fixeddrives;

    END

    CREATE TABLE #xp_fixeddrives

    (

    Drive varchar(250),

    MBFree int

    )

    INSERT INTO #xp_fixeddrives

    (

    Drive,

    MBFree

    )

    EXEC master..xp_fixeddrives

    SET @command = '

    USE [?]

    INSERT INTO #FileData

    (

    [CurrentHost],

    [ClusterNodes],

    [DB],

    [FileType],

    [Name],

    [VolumeOrDrive],

    [FileName],

    [File Size (MB)],

    [Space Used In File (MB)],

    [Available Space In File (MB)],

    [Drive Free Space (MB)]

    )

    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],

    CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],

    CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS [DB],

    CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],

    CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS [Name],

    CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS [VolumeOrDrive],

    CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS [FileName],

    CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],

    CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],

    CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],

    CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)]

    FROM dbo.sysfiles f WITH (NOLOCK)

    INNER JOIN sys.database_files df ON df.file_id = f.fileid

    LEFT JOIN tempdb..#xp_fixeddrives d

    ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'

    END

    ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)

    BEGIN

    SET @command = 'USE [?]

    INSERT INTO #FileData

    (

    [CurrentHost],

    [ClusterNodes],

    [DB],

    [FileType],

    [Name],

    [VolumeOrDrive],

    [FileName],

    [File Size (MB)],

    [Space Used In File (MB)],

    [Available Space In File (MB)],

    [Drive Free Space (MB)]

    )

    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],

    CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],

    CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],

    CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],

    CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],

    CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],

    CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],

    CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],

    CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],

    CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],

    CONVERT(Decimal(15,2), v.available_bytes/1048576.0) [Drive Free Space (MB)]

    FROM sys.sysfiles f WITH (NOLOCK)

    INNER JOIN sys.database_files df ON df.file_id = f.fileid

    CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'

    END -- END IF

    EXEC sp_MSforeachdb @command

    SELECT *

    FROM #FileData

    DROP TABLE tempdb..#FileData

    GO

     

    I have found this query which seems almost perfect for requirement can someone help me with adding two more columns one for db file autogrowth and other for total disk size..

    Thank you...

  • Which version of sql server are you using as there are now a set of DMV’s that expose this info, a lot easier!

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hello Perry,

    I want to fetch all these below mentioned columns, and we have versions from sql server 2008 to sql server 2016.

    Database Name

    Type

    File Name

    File Group Name

    File_Location

    FileSize_MB

    UsedSpace_MB

    FreeSpace_MB

    FreeSpace %

    Auto Growth

    volume_mount_point

    Total Volume Size_ GB

    Free Space_GB

    % Free

Viewing 13 posts - 1 through 12 (of 12 total)

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