Hi All,
Sharing this simple script through which once can list all database files with their size and space usage and space use percentage.
DECLARE @DataSpace TABLE (
DB varchar(150),
Filename nVARCHAR(max),
LogicalFileName nvarchar(100),
DataSizeMB Float,
DataUsed Float,
DataUsed_Percent Float
)
DECLARE @COMMAND nVARCHAR(500)
----- DB Space Checks ---------
SET @COMMAND ='USE [?]; select convert (varchar(150),db_name()) [Database Name] , a.physical_name ,a.name as LogicalFileName,convert(dec(15, 2), size * 8.0 / 1024) [File Size (MB)],
convert(dec(15, 2), fileproperty(a.name,''SpaceUsed'') * 8.0 / 1024) [File Used (MB)],convert(dec(15, 2), fileproperty(a.name,''SpaceUsed'') * 1.0 / size * 100) [% File Used]
from sys.database_files a left join sys.filegroups b on a.data_space_id = b.data_space_id'
INSERT INTO @DataSpace EXEC sp_msforeachdb @COMMAND
SELECT * from @DataSpace
--where DataUsed_Percent <60 AND DataSizeMB > 10240
Hope this helps!