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
sp_msforeachdbest une procédure non documentée. Le caractère?est remplacé par le nom de chaque base.- Une croissance en pourcentage n'a pas de valeur en Mo : la colonne
GrowthMBaffiche alors% warning!.