Email Alert

Posted by Barac in SQL Server on Mar 1st, 2018 | 0 comments

This is one simple example how you can use sp_send_dbmail stored procedure from msdb database to send DB backup size daily.

I used folowing arguments to achive this:

@profile_name (name of an existing Database Mail profile, type sysname, default NULL),
@recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)),
@body (Is the body of the e-mail message, nvarchar(max)),
@subject (Is the subject of the e-mail message, nvarchar(255)),
@body_format (Is the format of the message body, varchar(20))

More information about this system stored procedure you can find here sp_send_dbmail (Transact-SQL)

DECLARE
@emailSubject varchar(100),
@textTitle varchar(100),
@tableHTML nvarchar(max),
@Range date,
@DBName varchar(50)

SET @emailSubject = 'DB Backup Size'
SET @textTitle = 'DB Backup Size for the Last Month'
SET @Range=DATEADD(month, -1, CURRENT_TIMESTAMP)
SET @DBName = 'DBName'

set @tableHTML =
'<html>'+
'<head><style>'+
-- Data cells styles / font size etc
'td {border:1px solid #ddd;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt}'+
'</style></head>'+
'<body>'+
-- TextTitle style
'<div style="margin-top:15px; margin-bottom:15px; font-weight:bold; font-size:13pt; font-family:calibri;">' + @textTitle +'</div>' +
-- Color and columns names
'<div style="font-family:Calibri; "><table>'+'<tr bgcolor=#00881d>'+
'<td align=center><font face="calibri" color=White><b>Backup_Date</b></font></td>' + -- Backup Date
'<td align=center><font face="calibri" color=White><b>DB_Size(GB)</b></font></td>' + -- Size
'</tr></div>'

declare @body varchar(max)
select @body =
(
SELECT
td=(CAST(backup_finish_date as varchar(12))),
td=CONVERT(DECIMAL(16,2), isnull(backup_size / (1024 * 1024 * 1024), 0))
FROM msdb.dbo.backupset
where type = 'd' and database_name=@Dbname and backup_finish_date > @Range
order by backup_finish_date desc
for XML raw('tr'), elements
)

SET @body = REPLACE(@body, '<td>', '<td align=left><font face="calibri">')
SET @tableHTML = @tableHTML + @body + '</table></div></body></html>'
SET @tableHTML = '<div style="color:Black; font-size:8pt; font-family:Calibri; width:auto;">' + @tableHTML + '</div>'

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Your Database Mail Profile',
@recipients = 'recipient@email.com',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML'

And this is how final results should look like (Note, I do note have whole month backups for DB I used). You can create job and receive daily db backup size information.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / DB Backup Size Last Month / Email Alert

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud