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...
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
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
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%.
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',
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'
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