LLI Wiki — My Digital Knowledge Base

SQL Server : lister les fichiers de données et les filegroups

Lister les fichiers d'une instance ou d'une base, avec l'espace libre par filegroup et par volume.

Trois requêtes pour lister les fichiers d'une instance ou d'une base, avec leur emplacement, leur taille et l'espace libre des volumes qui les portent.

Fichiers et volumes de l'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);

Fichiers de la base courante

SELECT
  name 'Logical Name', 
  physical_name 'File Location'
FROM sys.database_files;

Espace libre par fichier et par filegroup, dans toutes les bases

La requête parcourt chaque base avec sp_msforeachdb et stocke le résultat dans une table temporaire globale.

	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

Remarques

Mastodon LLIMastodon SocialFosstodon