Recently I had to create a script which will monitor free space of all the datafiles and send alert if the free space within a datafile falls below 10% of the Total file space.
The following script should help to achieve the objective so sharing with the larger audience
SET NOCOUNT ON DECLARE @Tbl Table ( dbname varchar(30), file_id int, filename varchar(30), physicalname varchar(100), TotalSize int, Freespace int, PctFreespace int ) insert into @Tbl EXEC sp_MSforeachdb 'SELECT db_name(),[file_id],name,physical_name,size/128.0, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0, (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed") AS int)/128.0)*100/(size/128.0) FROM sys.database_files' IF EXISTS(select * from @Tbl where PctFreeSpace <= 40) BEGIN --<> select * from @Tbl where PctFreeSpace <= 40 END
Hope this helps !!!
Parikshit Savjani
Premier Field Engineer