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’

*100/(size/128.0) FROM sys.database_files’ IF EXISTS(select * from @Tbl where PctFreeSpace <= 40) BEGIN –<<Send Alert>> select * from @Tbl where PctFreeSpace <= 40 END

 

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer