Technical Article

Get file sizes of database files and free space on disk

,

This script can be used from a Registered Server Group to obtain space information about all servers. Results are returned in one result-set allowing easy copy/pasting into Excel where pivot tables can be created to summarise the data.
Data returned:
[CurrentHost]
Machine that's hosting the database instance
[ClusterNodes]
If the database is clustered returns all the nodes in the cluster
[DB]
The database name
[FileType]
Either LOG or ROW indicates if it's a log file or data file.
[Name]
Name of the database file
[VolumeOrDrive]
Either the drive( if SQL Server version 2008 or lower) or the mounted volume if SQL 2008 R2 or higher.
[FileName]
Full file path
[File Size (MB)]
Size of the database file in MB
[Space Used In File (MB)]
Amount of space used within the database file in MB
[Available Space In File (MB)]
Amount of free space within the database file  in MB
[Drive Free Space (MB)]
Amount of free space available on the drive or volume in MB.
-- =============================================
-- Author:Jonathan Roberts
-- Create date: 2015-07-23
-- Description:Script to display the database data and log file sizes 
--              and available space on the volume.
--              Can be run on a registered server group to get data for all database servers
-- =============================================
GO

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

Rate

4.14 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

4.14 (7)

You rated this post out of 5. Change rating