SQL Server: list data files and filegroups
List the files of an instance or a database, with the free space per filegroup and per volume.
Three queries to list the files of an instance or a database, with their location, size and the free space of the volumes that hold them.
Files and volumes of the instance
SELECT name, volume_mount_point, logical_volume_name, file_system_type, physical_name, type_desc, state_desc FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
Files of the current database
SELECT
name 'Logical Name',
physical_name 'File Location'
FROM sys.database_files;
Free space per file and filegroup, in all databases
The query loops over each database with sp_msforeachdb and stores the result in a global temporary table.
Create Table ##temp
(
[DatabaseName] [nvarchar](128) NULL,
[Filegroup] [sysname] NOT NULL,
[FileID] [int] NOT NULL,
[LogicalName] [sysname] NOT NULL,
[Path] [nvarchar](260) NULL,
[FileSizeMB] [decimal](18, 2) NULL,
[FreeSpaceMB] [decimal](18, 2) NULL,
[FreeSpace%] [decimal](5, 2) NULL,
[GrowthMB] [varchar](41) NULL,
[MaxSizeMB] [decimal](18, 2) NULL,
[DriveSizeMB] [bigint] NULL,
[DriveFreeMB] [bigint] NULL,
[DriveFree%] [decimal](5, 2) NULL)
Exec sp_msforeachdb '
Use [?];
;WITH src AS
(
SELECT FG = fg.name,
FileID = f.file_id,
LogicalName = f.name,
[Path] = f.physical_name,
FileSizeMB = f.size/128.0,
UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], ''SpaceUsed''))/128.0,
GrowthMB = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128.0 END,
MaxSizeMB = NULLIF(f.max_size, -1)/128.0,
DriveSizeMB = vs.total_bytes/1048576.0,
DriveFreeMB = vs.available_bytes/1048576.0
FROM sys.database_files AS f
INNER JOIN sys.filegroups AS fg
ON (f.data_space_id = fg.data_space_id ) -- or f.data_space_id=0)
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs
--WHERE fg.name = COALESCE(@FileGroupName, fg.name)
)
insert into ##temp([DatabaseName],[Filegroup],[FileID] ,[LogicalName] ,[Path],[FileSizeMB] ,[FreeSpaceMB] ,[FreeSpace%] ,[GrowthMB] ,[MaxSizeMB] ,[DriveSizeMB] ,[DriveFreeMB] ,[DriveFree%] )
SELECT DB_NAME() AS [DatabaseName],[Filegroup] = FG, FileID, LogicalName, [Path],
FileSizeMB = CONVERT(decimal(18,2), FileSizeMB),
FreeSpaceMB = CONVERT(decimal(18,2), FileSizeMB-UsedSpaceMB),
[FreeSpace%] = CONVERT(decimal(5,2), 100.0*(FileSizeMB-UsedSpaceMB)/FileSizeMB),
GrowthMB = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), ''% warning!''),
MaxSizeMB = CONVERT(decimal(18,2), MaxSizeMB),
DriveSizeMB = CONVERT(bigint, DriveSizeMB),
DriveFreeMB = CONVERT(bigint, DriveFreeMB),
[DriveFree%] = CONVERT(decimal(5,2), 100.0*(DriveFreeMB)/DriveSizeMB)
FROM src
ORDER BY FG, LogicalName;
'
Select * From ##temp
drop table ##temp
Notes
sp_msforeachdbis an undocumented procedure. The?character is replaced by the name of each database.- A percentage growth has no value in MB: the
GrowthMBcolumn then shows% warning!.