Get disk space usage of database files
Sp_helpfile is an option to query the size of the database file but it does not give how much disk space out of the allocated is being used by the data or log file. Below is the query which can be used to fetch the details like disk space allocated to each data and log file for the database and how much is used and how much is free. It also gives the physical location of the files.
USE DatabaseName;
GO
SELECT NAME AS LogicalFileName
,CAST(size / 128.0 AS DECIMAL(20, 2)) [Total(MB)]
,CAST(CAST(FILEPROPERTY(NAME, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(20, 2)) AS [Used(MB)]
,CAST((size - CAST(FILEPROPERTY(NAME, 'SpaceUsed') AS INT)) / 128.0 AS DECIMAL(20, 2)) [Remaining(MB)]
,physical_name AS Path
FROM sys.database_files