Script to monitor Free Space of the data file and send alert

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