Xrhrft

Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

(you would need to change the math for TBs):

-- Individual File Sizes and space available for current database

-- (Query 36) (File Sizes and Space)

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 

CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],

CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 

AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]

FROM sys.database_files AS f WITH (NOLOCK) 

LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 

ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

Here's some SQL that might work for you.

Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 

       filName varchar(520) NULL, sizeMg decimal(10,2) null, 

       usedMg decimal(10,2) null, freeMg decimal(10,2) null, 

       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)

Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 

Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo Set

freeMg = sizeMg - usedMg,

pcntUsed = (usedMg/sizeMg)*100,

pcntFree = ((sizeMg-usedMg)/sizeMg)*100



select * from #dbInfo compute sum(sizeMG),  sum(FreeMg) 

drop table #dbInfo

It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.

After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.

This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).

SELECT fg.data_space_id AS FGID,

   (f.file_id) AS File_Id,

   -- As provided by OP, size on disk in bytes.

   REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,

   ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,

   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,

   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,

   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,

   f.name,

   f.physical_name

FROM sys.database_files f 

    LEFT JOIN sys.filegroups fg

    ON f.data_space_id = fg.data_space_id

In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,

dovs.volume_mount_point AS Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY FreeSpaceInMB ASC

USE [db]

GO



/****** Object:  StoredProcedure [dbo].[GetDriveSpace]    Script Date: 6/29/2018 9:22:12 AM ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO











CREATE PROCEDURE [dbo].[GetDriveSpace]



    @DaysToRetain           SMALLINT    = 90



AS



SET NOCOUNT ON



-- Persisted work table



IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL

    BEGIN

      Create TABLE dbo.DriveSpace

        (

            PK                                      INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,

            DriveLetter                             CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            VolumeName                              VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            AvailableMB                             BIGINT NOT NULL,

            TotalDriveSpaceMB                        BIGINT NOT NULL,

            [TotalSpaceGB]  AS ([TotalDriveSpaceMB]/(1024.00)),

            [FreeSpaceGB]  AS ([AvailableMB]/(1024.00)),    

            PercentFree                             AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),               

            EntryDate                               DATETIME NOT NULL

        ) ON [PRIMARY]

        CREATE NONCLUSTERED INDEX NCnd_EntryDate

        ON dbo.DriveSpace (EntryDate)

    END



-- Vars

DECLARE



    @AgeOutDate                                     DATETIME,       -- To prune the [DriveSpace] table

    @BATCHDATE                                      DATETIME,       -- Constant

    @DriveSize                                      BIGINT,         -- Drive size in bytes

    @DriveLetter                                    VARCHAR(2),     -- FSO drive letter

    @ErrorMessage                                   VARCHAR(500),   -- Error message

    @FSO                                            INT,            -- File System Object

    @AvailableMB                                    INT,            -- Available space MB

    @NO                                             BIT,            -- Constant

    @oDrive                                         INT,            -- FSO drive

    @OLE_AUTOMATION_PROCEDURES                      INT,            -- Constant

    @PK                                             TINYINT,        -- Work-table primary key

    @RawFree                                        VARCHAR(20),    -- Available space bytes

    @RetVal                                         INT,            -- Return value

    @SQL                                            NVARCHAR(255),  -- Dynamic T-SQL

    @TwoToTheTwentieth                              INT,            -- Convert bytes to MB

    @ValueInUse                                     SQL_VARIANT,    -- To test configuration settings

    @VolumeName                                     VARCHAR(32),    -- FSO volume name

    @YES                                            BIT             -- Constant



-- Constants

SET @BATCHDATE                          = GETDATE()

SET @OLE_AUTOMATION_PROCEDURES          = 16388         -- SELECT configuration_id FROM master.sys.configurations

SET @NO                                 = 0

SET @TwoToTheTwentieth                  = POWER(2, 20)

SET @YES                                = 1



-- Create temp tables for disk space info

IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space

CREATE TABLE #Space

(

    PK                      TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,

    DriveLetter             VARCHAR(2) NOT NULL,

    VolumeName              VARCHAR(32) NULL,

    DriveSize               BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),

    AvailableMB             BIGINT NOT NULL,

    Processed               BIT CONSTRAINT DF_Space_Processed DEFAULT (0)

)



-- We need OLE automation enabled to succeed.



BEGIN TRY

    -- Enable advanced options

    EXEC sp_configure 'show advanced option', '1';

    RECONFIGURE;



    -- Ensure OLE Automation enabled

    EXEC sp_configure 'OLE Automation Procedures', @YES;

    RECONFIGURE



    -- Test...

    SELECT @ValueInUse = Value_In_Use

    FROM master.sys.configurations

    WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES



    IF CONVERT(INT, @ValueInUse) <> @YES

        BEGIN

            SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)

                + 'This script depends upon this extended stored procedure''s availability. Please investigate.'

                + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'



            RAISERROR(@ErrorMessage, 16, 1)

        END



END TRY



BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber

        ,ERROR_MESSAGE() AS ErrorMessage

        ,ERROR_SEVERITY() AS ErrorSeverity

        ,ERROR_STATE() AS ErrorState

        ,ERROR_LINE() AS ErrorLine

        ,ERROR_PROCEDURE() AS ErrorProcedure



        GOTO Cleanup

END CATCH



-- 1) Remove records older than user-provided number of days



BEGIN TRY



    -- By default, I'd like to keep 2 years of data

    SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())



    DELETE dbo.DriveSpace

    WHERE EntryDate < @AgeOutDate



END TRY



BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber

        ,ERROR_MESSAGE() AS ErrorMessage

        ,ERROR_SEVERITY() AS ErrorSeverity

        ,ERROR_STATE() AS ErrorState

        ,ERROR_LINE() AS ErrorLine

        ,ERROR_PROCEDURE() AS ErrorProcedure



        GOTO Cleanup

END CATCH



-- 2) Get free space on drives



BEGIN TRY

    -- Populate temp table; this system proc gives us space in MB

    INSERT INTO #Space (DriveLetter, AvailableMB)

        EXEC master.dbo.xp_fixeddrives



-- 3) Loop through drives and get various attributes using OLE automation



    WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)



        BEGIN



            SELECT TOP 1

                @PK             = PK,

                @DriveLetter    = DriveLetter,

                @AvailableMB    = AvailableMB,

                @DriveSize      = DriveSize

            FROM #Space

            WHERE Processed = @No



            BEGIN TRY

                EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT

            END TRY



            BEGIN CATCH

                SELECT

                    ERROR_NUMBER() AS ErrorNumber

                    ,ERROR_MESSAGE() AS ErrorMessage

                    ,ERROR_SEVERITY() AS ErrorSeverity

                    ,ERROR_STATE() AS ErrorState

                    ,ERROR_LINE() AS ErrorLine

                    ,ERROR_PROCEDURE() AS ErrorProcedure



                    GOTO Cleanup

            END CATCH           



            BEGIN TRY



                -- Get disk info

                SET @DriveLetter = @DriveLetter + ':'

                EXEC master.dbo.sp_OAMethod @FSO,           'GetDrive',         @oDrive OUT, @DriveLetter

                EXEC master.dbo.sp_OAMethod @oDrive,        'TotalSize',        @DriveSize OUT

                EXEC master.dbo.sp_OAMethod @oDrive,        'AvailableSpace',   @RawFree OUT

                EXEC master.dbo.sp_OAGetProperty @oDrive,   'VolumeName',       @VolumeName OUT         



            END TRY



            BEGIN CATCH

                SELECT

                    ERROR_NUMBER() AS ErrorNumber

                    ,ERROR_MESSAGE() AS ErrorMessage

                    ,ERROR_SEVERITY() AS ErrorSeverity

                    ,ERROR_STATE() AS ErrorState

                    ,ERROR_LINE() AS ErrorLine

                    ,ERROR_PROCEDURE() AS ErrorProcedure



                    GOTO Cleanup

            END CATCH           



            BEGIN TRY

                -- Update table

                UPDATE #Space

                SET

                    VolumeName =

                        CASE LOWER(@DriveLetter)

                            WHEN 'c:' THEN 'System'

                            ELSE @VolumeName

                        END,

                    -- Convert bytes to megabytes

                    DriveSize       = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,

                    -- Convert bytes to megabytes

                    AvailableMB     = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth

                WHERE PK = @PK;



            END TRY



            BEGIN CATCH

                SELECT

                    ERROR_NUMBER() AS ErrorNumber

                    ,ERROR_MESSAGE() AS ErrorMessage

                    ,ERROR_SEVERITY() AS ErrorSeverity

                    ,ERROR_STATE() AS ErrorState

                    ,ERROR_LINE() AS ErrorLine

                    ,ERROR_PROCEDURE() AS ErrorProcedure



                    GOTO Cleanup

            END CATCH           



            BEGIN TRY



                -- Destroy oDrive

                EXEC master.dbo.sp_OADestroy @oDrive



            END TRY



            BEGIN CATCH

                SELECT

                    ERROR_NUMBER() AS ErrorNumber

                    ,ERROR_MESSAGE() AS ErrorMessage

                    ,ERROR_SEVERITY() AS ErrorSeverity

                    ,ERROR_STATE() AS ErrorState

                    ,ERROR_LINE() AS ErrorLine

                    ,ERROR_PROCEDURE() AS ErrorProcedure



                    GOTO Cleanup

            END CATCH           



            UPDATE #Space

            SET Processed = @YES

            WHERE PK = @PK



        END



    BEGIN TRY



        -- Destroy FSO

        EXEC master.dbo.sp_OADestroy @FSO



    END TRY



    BEGIN CATCH

        SELECT

            ERROR_NUMBER() AS ErrorNumber

            ,ERROR_MESSAGE() AS ErrorMessage

            ,ERROR_SEVERITY() AS ErrorSeverity

            ,ERROR_STATE() AS ErrorState

            ,ERROR_LINE() AS ErrorLine

            ,ERROR_PROCEDURE() AS ErrorProcedure



            GOTO Cleanup

    END CATCH



-- 4) Load results in table



    -- Return disk-space info

    INSERT dbo.DriveSpace

    (

       DriveLetter,

       VolumeName,

       AvailableMB,

       TotalDiskSpaceMB,

       EntryDate

    )



    SELECT

        DriveLetter                                                     DriveLetter,

        CASE

            WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'

            ELSE VolumeName

        END                                                             VolumeName,

        AvailableMB                                                     AvailableMB,

        DriveSize                                                       TotalDriveSpaceMB,

        @BATCHDATE                                                      LoadDate

        FROM #Space



END TRY



BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber

        ,ERROR_MESSAGE() AS ErrorMessage

        ,ERROR_SEVERITY() AS ErrorSeverity

        ,ERROR_STATE() AS ErrorState

        ,ERROR_LINE() AS ErrorLine

        ,ERROR_PROCEDURE() AS ErrorProcedure



        GOTO Cleanup

END CATCH



Cleanup:



-- Ensure xp_cmdshell's off

 EXEC sp_configure 'OLE Automation Procedures', @NO;

 RECONFIGURE







GO

Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.

It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses

--drop table ##DB_FILE_INFO

 --alter procedure super_drive_info3 



 --as



  if exists ( select * from tempdb.dbo.sysobjects o

    where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))

drop table ##DB_FILE_INFO



if exists ( select * from tempdb.dbo.sysobjects o

    where o.xtype in ('U') and o.name in ('##output'))

drop table ##output



 create table ##DB_FILE_INFO (

[server_Name] varchar(255) not null,

[database_name] varchar(255) not null,

[File_ID]           int     not null,

[File_Type]     int     not null ,

[Drive]     varchar(255)    not null ,

[Logical_Name]      varchar(255)    not null ,

[Physical_Name]     varchar(255)    not null ,

[FILE_SIZE_MB]      int     not null ,

[SPace_USED_MB] int     not null ,

[Free_space]    int     not null ,

[Max_SIZE]      int     not null ,

[Percent_Log_growth_enabled]    int     not null ,

[growth_rate]   int     not null ,

[current_date]      datetime        not null

)

--go

declare @sql    nvarchar(4000)

set @sql =

'use ['+'?'+']

 --if db_name() <> N''?'' goto Error_Exit

 insert into ##DB_FILE_INFO

(

    [server_Name],

    [database_name],

    [File_ID],

[File_Type],

[Drive],

[Logical_Name],

[Physical_Name],

[FILE_SIZE_MB],

[SPace_USED_MB],

[Free_space],

[Max_SIZE],

[Percent_Log_growth_enabled],

[growth_rate],

[current_date]

)

SELECT 

@@servername as [Server_Name],

db_name() as database_name,

[file_id] AS [File_ID],

[type] AS [File_Type],

substring([physical_name],1,1) AS [Drive],

[name] AS [Logical_Name],

[physical_name] AS [Physical_Name],

CAST([size] as DECIMAL(38,0))/128. AS [File Size MB], 

CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS 

[Space_Used_MB], 

(CAST([size] AS DECIMAL(38,0))/128) - 

(CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free 

 Space],

[max_size] AS [Max_Size],

[is_percent_growth] AS [Percent_Growth_Enabled],

[growth] AS [Growth Rate],

getdate() AS [Current_Date]

FROM sys.database_files'

 exec sp_msforeachdb @sql

 declare @svrName varchar(255)

declare @sql2 varchar(400)

--by default it will take the current server name, we can the set the server 

name as well

set @svrName = @@SERVERNAME

set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' + 

QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | 

select name,capacity,freespace | 



foreach{$_.name+''|

 ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

 --creating a temporary table

 CREATE TABLE ##output

 (line varchar(255))

 --inserting  in to temporary table

 insert ##output

 EXEC xp_cmdshell @sql2;



 with Output2 

 --(drivename, capacity(gb),freespace(gb), always_on_status)

 as

 (

 select @@servername as server_name, 

rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename

 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

 (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as 

 'capacityGB'

 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

 (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as 

 'freespaceGB'

 ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN 

 (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS 

 ALWAYS_ON_STATUS

  --into #output2

 from ##output

   where line like '[A-Z][:]%'

   --order by drivename

  ),

  DB_FILE_INFO2 as

 (

 select server_Name,

    database_name,

    File_ID,

File_Type,

Drive,

Logical_Name,

Physical_Name,

FILE_SIZE_MB,

SPace_USED_MB,

Free_space,

Max_SIZE,

Percent_Log_growth_enabled,

growth_rate

--current_date 

from ##DB_FILE_INFO 

--inner join #output b on a.drive = b.drivename and a.server_Name = 

b.server_name

)

select  

getdate() as Today_Date,

a.server_Name,

a.database_name,

a.Drive,

a.Logical_Name,

a.Physical_Name,

a.FILE_SIZE_MB,

a.Space_Used_MB,

--sum(a.SPace_USED_MB) as hg,

a.Free_space as Free_Space_in_File,

 --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),

b.capacitygb as Total_Drive_capacity,

b.freespacegb as Total_Free_Space,

c.total_log_size_mb,

c.active_log_size_mb

--,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB 

as decimal)) * 100)



from DB_FILE_INFO2 a

inner join output2 b on a.server_Name = b.server_name and a.Drive = 

 b.drivename

cross apply sys.dm_db_log_stats (db_id(a.database_name)) c

order by a.Drive, a.database_name



--drop table ##DB_FILE_INFO

--drop table #output

How about asking M$ for some really simple view that you can "select * from"?

Instead everyone is doing some extra functins, extra selects, extra tables and so on...

If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....