Script to find space utilization in each data file and log file

by

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!

Post Views: 159