SQLTeam.com | Weblogs | Forums

Email Alert


#1

I am trying to create a email alert script within sql server 2014 to send automated emails to users in which we set to get notified that the database is going over 80% disk usage space.

Please see what I have so far.....I need it ot check and disk space and send an email alert and set it up to where it executes a scheduled job to omplete twice a day at 8am and 6pm.

This is rather tough for me and havent seem to really get far...here is what I have so far...

SELECT DISTINCT dovs.logical_volume_name LogicalName,

dovs.volume_mount_point Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY FreeSpaceInMB ASC

GO


#2

What do you need to send? Here's a script to get %free as well.

SELECT
   [Drive] = volume_mount_point
  ,[FreeSpaceMB] = available_bytes/1024/1024 --/1024
  ,[SizeMB] = total_bytes/1024/1024 --/1024
  ,[PercentFree] = CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
FROM sys.master_files mf
  CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id)
--Optional where clause filters drives with more than 20% free space
WHERE CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) < 20
GROUP BY
   volume_mount_point
  ,total_bytes/1024/1024 --/1024
  ,available_bytes/1024/1024 --/1024
  ,CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
ORDER BY [Drive]

#3

you have a few options

  • Scheduled SQL Job which leverages sp_send_dbmail
  • SSIS package which would send an email only when 80% threshold is reached. This might be overkill.
  • SSRS report and subscription to leverage it's own email capabilities, self service in that users can go to report any time they want. more of a pull than push

#4

I need to have the sql server database send automated email alerts when the disk space is above 80% and checks it every day ...so similar to a job


#5

I am using database mail


#6

Does it make a difference what year. I am using sql server 2014


#7

this works for me, sends a csv file, you can pretty it if you want as html? of course change emails and smtp servers to yours. This might require some configuration on your SQL server before it works.

/*
	--helpers and cleaners
	exec msdb.dbo.sysmail_help_account_sp
	exec msdb.dbo.sysmail_help_profile_sp  
	exec msdb.dbo.sysmail_help_profileaccount_sp
	exec msdb.dbo.sysmail_help_principalprofile_sp

	exec msdb.dbo.sysmail_delete_account_sp @account_name = 'Disk Alert'
	exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Disk Alert Profile'
	exec msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Disk Alert Profile'
	exec msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Disk Alert Profile'
*/
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'Disk Alert',  
    @description = '.',  
    @email_address = 'trequonw@trequonw.com',  
    @replyto_address = 'trequonw@trequonw.com',  
    @display_name = 'Disk Alert Automated Mailer',  
    @mailserver_name = 'smtp.youtsmtp_server.com' ;  

-- Create a Database Mail profile  

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Disk Alert Profile',  
    @description = 'Profile used for administrative mail.' ;  

-- Add the account to the profile  

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Disk Alert Profile',  
    @account_name = 'Disk Alert',
    @sequence_number =1 ;  

-- Grant access to the profile to all users in the msdb database  

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'Disk Alert Profile',  
    @principal_name = 'public',  
    @is_default = 1 ;  

declare @query varchar(max) = '

SELECT
   [Drive] = volume_mount_point
  ,[FreeSpaceMB] = available_bytes/1024/1024 --/1024
  ,[SizeMB] = total_bytes/1024/1024 --/1024
  ,[PercentFree] = CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
FROM sys.master_files mf
  CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id)
--Optional where clause filters drives with more than 20% free space
--WHERE CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) < 20
GROUP BY
   volume_mount_point
  ,total_bytes/1024/1024 --/1024
  ,available_bytes/1024/1024 --/1024
  ,CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
ORDER BY [Drive]'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Disk Alert Profile',
    @recipients='trequonw@trequonw.com',
    @subject='Disk Usage Alert', 
    @body='See attachment',
    @query =@query,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'CSV_Extract.txt',
    @query_result_separator = ',',
    @query_result_header = 1