LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon