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

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *