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

#8

Thank You


#10

I am trying to make the above script into a store procedure in which it should be executed by anightly job once a day. Can you walk me through this? I am having a hard time getting this done. The script should be simple to simply notify users of low disk space when the usage reaches above 80%.


#11

create procedure your_stored_procedure
as
begin
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]
end
go

 @query = 'exec dbo.your_stored_procedure' ,
 @execute_query_database = 'your_database',

#12

Thank you so much for helping me. I am still new to this. SO lets says my database IP address is 155.7.199.23 would I enter that in the quotes where you put "your_database"

what exactly do I replace with the "exec dbo.your_stored_procedure' ?

would that be the following ?

exec msdb.dbo.sp_send_dbmail
@profile_name = 'DV199017',
@recipients = 'david.d.ctr@mil trequon.wallace.ctr@mil',
@body = 'I am testing this from db mail ',
@subject = 'DB email from TSQL'


#13

Please read this

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017


#14

your_database is where you created the stored procedure in. no need to enter a server ip address unless the stored procedure was created on another server that you are connecting to using a linked server in which case I would not create it using ip.address.

you replace "exec dbo.your_stored_procedure' with exactly what it says, your stored procedure you created to fetch the data namely the stored procedure provided for you in

create procedure your_stored_procedure
as