I didn't realize that one of our former DBA's created a SQL agent job on our old server but never enabled it on the new database. The script runs every night. It's something, but it would be better if you only get a message when you are close to max'ing out. I am going to go and set it to unlimited. As you mentioned an outage is not a good thing. I think that is prudent and appreciate your opinions on that. We do have warnings if the drive space is getting tight. Right now btw it increases in increments of 1024kb.
Declare @MailBody varchar(5000) = '';
Declare @msg varchar(5000) = '';
Declare @filename varchar(500) = '';
Declare @Physicalname varchar(500) = '';
Declare @PercentAvailable int = 0;
declare c_t cursor for
SELECT name AS 'FileName' ,
physical_name AS 'PhysicalName',
round((100 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/max_size)* 100) ,2) AS 'PercentageAvailable'
FROM sys.database_files
where (max_size/128) != 0 and type_desc = 'ROWS'
and ((CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/max_size)* 100) > 80;
open c_t
fetch next from c_t into @filename, @PhysicalName, @PercentAvailable
while @@FETCH_STATUS = 0
BEGIN
select @MailBody = @MailBody + '<tr><td>' + @FileName + '</td><td>' + @PhysicalName + '</td><td>' + cast(@PercentAvailable as varchar(10)) + '</td></tr>'
fetch next from c_t into @filename, @PhysicalName, @PercentAvailable
END
close c_t
deallocate c_t
if (@MailBody != '')
BEGIN
select @msg = '<table><tr><td><b>File name</b></td><td><b>Physical file name</b></td><td><b>Percent Available</b></td></tr>' + @MailBody + '</table>';
EXEC msdb.dbo.SP_SEND_DBMAIL
@profile_name='smtp',
@recipients='xxx2@xxx.org;xxx@xxx.org',
@subject = 'OnBasePRD data files percent available',
@body = @msg,
@body_format = 'HTML'
END
