Oh my goodness, are we back on the log-shipping journey? We are! After the last post, I found a bunch of things that I wanted to fix and one of them was alerting. How do I know if my logs are applying? How do I know if something bad is happening?
I don’t want emails that tell me everything is ok, only when things are bad and I want them to be helpful emails. Not only did I want an alert, I wanted an email with actual information that I can use to make my decisions. Decisions like, can I just apply a few logs to get caught up or did everything burn down and I need to pull a full backup plus all the logs to be up and running again?
This was a task for some super fancy alerts on my agent job.
First, I need to come up with the query of information I wanted to display in my email:
SELECT Max(CreatedDate) AS LastRestore, Origin AS [Database]
FROM RestoreFile
WHERE IsApplied = 1
GROUP BY Origin
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3This query will give me the LastRestored file time and for which Database it was restored. It will help me identify which database is having problems and how much I need to try to fix it. So, cool query, but how do I get that to email me? This next part saves me so much time with digging on my agent job issues.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [LastRestore] AS 'td','',[Database] AS 'td'
FROM (SELECT Max(CreatedDate) AS LastRestore, Origin AS [Database]
FROM RestoreFile
WHERE IsApplied = 1
GROUP BY Origin
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3) A
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Last Applied Log</H3>
<table border = 1>
<tr>
<th> Last Restore </th> <th> Database </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
IF (SELECT TOP 1 COUNT (DISTINCT(Origin))
FROM RestoreFile
WHERE IsApplied = 1
GROUP BY Origin
HAVING DATEDIFF(hh,Max(CreatedDate),GETDATE()) > 3) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'andrea@WayneManor.com', -- replace with your email address
@subject = 'Transaction Logs Are Old' ;
ENDA few things are happening here. First I am declaring my @xml and my email @body. Then I create the query that is going to return my formatted table in my email and shove that into XML. Next, I start building the body of the email with a title for my table, and column names too (using HTML). Then I combine it with the XML.
The “If ” statement allows me to only send the email if it meets conditions in the query. This query is similar to the one I created above, but it doesn’t have to be. Also, the count has to be greater than 0 to send the email, otherwise it will skip sending.
Finally, I get to the part that calls sp_send_dbmail. This allows me to set the profile for the sender, add in my XML\HTML built body and tell the proc to use that HTML formatting. I add my email recipients and a subject for the email.
I started testing it and it was awesome…until one day when I got an error from what is reading my databases because one wasn’t in Standby. I decided to add another step that would also check that all the databases were in Standby. It is almost exactly the same, with the exception of the query, which looks like this:
SELECT name as [Database]
FROM sys.databases
WHERE is_read_only <> 1 -- Read_only should equal 1 if the database is in standby.
AND database_id > 5 -- 5 is my database that is controlling the log-shipping and lower are system databases.I have these emails also forward me a text so I know that something is wrong. It is awesome and makes me worry so much less.
The song for this post is Imagine Dragons’ Wrecked.
Andrea Allred presents RoyalSQL 