Infrastructure at your Service

Steven Naudet

SQL Server: Get email notifications for Error Log entries

Introduction

In a previous blog post, I did a T-SQL script reading Audit files to send emails with valuable information about instance changes.
In this one, you will see how to get nice emails from critical errors or specific events occurring on the instance using the ErrorLog.

A common best practice for any DBA is to configure SQL Server Agent Alert for errors with severity 17 to 25.

Alerts are also often set for less critical errors like “Login failed” (severity level 14).
Emails sent from Alerts are very basic and looks like this:

All these errors are written to the SQL Server Error Log file.

The text message for these error messages that are logged into the Error Log can be found in the sys.messages view.

select message_id, severity, text
from sys.messages 
where (severity between 18 and 21
   or severity = 14)
  and is_event_logged = 1 
  and language_id = 1033
order by severity;

This query returns 174 rows on SQL Server version 2019.

The error log files can be read using the stored procedure sp_reaerrorlog.

Searching in the Error Log in SQL

The aim of the T-SQL script I am writing now is to replace Alerts with a job running every 5 minutes on my instance that will send me emails based on some messages I will define.

Knowing all the information we’ve seen before, I can build a table variable with the text that I want to look for in the ErrorLog, whether it is critical errors or informational messages.

DECLARE @errorlog_definition TABLE(
	error_category varchar(150)
	, error_pattern varchar(1000)
)
insert into @errorlog_definition
	values ('Database Write Latency', '%I/O requests taking longer than%seconds to complete%')
		,('Database Write Latency', '%cleaned up%bufs with%in%ms%for db%')
		,('Database Write Latency', '%average%')
		,('Database Write Latency', '%last target outstanding:%avgWriteLatency%')
		,('Database Write Error Disk Full', 'Could not allocate%')
		,('Database Login Failure', '%Login Failed%')
		,('SQL Server starting', 'SQL Server is starting%')

The first column is the type of event I want to be emailed about and will be used in the email title.

The current error log entries are inserted in another table variable.

insert into @errorlog
	exec sp_readerrorlog 0

Now, using a CROSS APPPY I can get only the Error Log entries matching my messages patterns.

select e.LogDate, e.ProcessInfo, e.Text, c.error_category
from @errorlog AS e
	cross apply (
		select *
		from @errorlog_definition AS d
		where e.Text like d.error_pattern
	) AS c

I managed to get only the Error Log entries I want. Entries are categorized and I decided not to send just one email every time the job runs but once for every category.

I did this with cursors. Here is the whole script if you want to try it.

Script

--	Job frequency
DECLARE @CheckPeriodInMinute int = 5

--	Variables
DECLARE @colldate datetime         
      , @object varchar(1024)
      , @HTML_footer varchar(max)  
      , @HTML_header varchar(max)  
      , @HTML varchar(max)         
	  , @HTML_part varchar(max)    

DECLARE @errorlog TABLE(
	  LogDate datetime
	, ProcessInfo varchar(32)
	, Text varchar(max)
)
DECLARE @notifiable_errors table(
	  LogDate varchar(19)
	, ProcessInfo varchar(32)
	, Text varchar(1024)
	, error_category varchar(150)
)

SELECT @HTML_header='
<head>
<style type="text/css">
table {border: 1px solid #1C6EA4;background-color: #EEEEEE;width: 100%;text-align: left;border-collapse: collapse;}
table td, table th {border: 1px solid #AAAAAA;padding: 3px 2px;}
table tbody td {font-size: 13px;}
table thead {background: #1C6EA4;border-bottom: 2px solid #444444;}
table thead th {font-size: 15px;font-weight: bold;color: #FFFFFF;border-left: 2px solid #D0E4F5;}
table thead th:first-child {border-left: none;}
h1      {font:bold 16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:15pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
</style>
</head>
<body>
<a name="top"></a>'

set @HTML_footer='<p><i>Executed on '+convert(varchar,getdate(),120)+' from server "'+isnull(convert(varchar(128),@@SERVERNAME),'')+'"</i></p>'

--	----
DECLARE @errorlog_definition TABLE(
	error_category varchar(150)
	, error_pattern varchar(1000)
)
insert into @errorlog_definition
	values ('Database Write Latency', '%I/O requests taking longer than%seconds to complete%')
		,('Database Write Latency', '%cleaned up%bufs with%in%ms%for db%')
		,('Database Write Latency', '%average%')
		,('Database Write Latency', '%last target outstanding:%avgWriteLatency%')
		,('Database Write Error Disk Full', 'Could not allocate%')
		,('Database Login Failure', '%Login Failed%')
		,('SQL Server starting', 'SQL Server is starting%')

insert into @errorlog
	exec sp_readerrorlog 0

-- Get Error Log entries matching pattern (like)
insert into @notifiable_errors
	select e.LogDate, e.ProcessInfo, e.Text, c.error_category
	from @errorlog AS e
		cross apply (
			select *
			from @errorlog_definition AS d
			where e.Text like d.error_pattern
		) AS c
	where LogDate > DATEADD(MINUTE, [email protected], GETDATE())

-- If any rows to process
if @@ROWCOUNT>0
begin

	DECLARE @logdate datetime
	DECLARE @processInfo varchar(32)
	DECLARE @Text varchar(MAX)
	DECLARE @error_category varchar(150)

	DECLARE category_cursor CURSOR FOR 
		select distinct error_category
		from @notifiable_errors

	OPEN category_cursor  
	FETCH NEXT FROM category_cursor INTO @error_category

	WHILE @@FETCH_STATUS = 0  
	BEGIN
		-- Loops 1 time per category
		
		-- Email Object + HTML Table header
		SELECT @object = @error_category+' - Last '+convert(varchar(50),@CheckPeriodInMinute)+' min ('+convert(varchar(max),@@servername)+')'
		SELECT @[email protected]_header+'<h1>'[email protected]_category+' (last '+convert(varchar(50),@CheckPeriodInMinute)+' min)</h1>'
		
		SELECT @[email protected]+'
		<table>
		<tr><th>LogDate</th><th>ProcessInfo</th><th>Text</th></tr>
		'
		--	----
		--	Cursor: Get all entries for the current category
		--	Create HTML Table rows
		--	----
		DECLARE error_cursor CURSOR FOR 
			select LogDate, ProcessInfo, Text
			from @notifiable_errors
			where error_category = @error_category

		OPEN error_cursor  
		FETCH NEXT FROM error_cursor INTO @logdate, @processInfo, @Text
		WHILE @@FETCH_STATUS = 0  
		BEGIN
			-- HTML Table rows
			select @HTML_part = '<tr><td>'+isnull(convert(varchar,@logdate,120),'')+'</td><td>'+isnull(@processInfo,'')+'</td><td>'+isnull(@Text,'')+'</td></tr>'
			set @[email protected][email protected]_part+'
			'

			FETCH NEXT FROM error_cursor INTO @logdate, @processInfo, @Text;
		END
		CLOSE error_cursor  
		DEALLOCATE error_cursor 
		
		-- HTML Table end + send email
		set @[email protected]+'</table>
		'[email protected]_footer
	
		EXEC msdb.dbo.sp_send_dbmail
		  @profile_name = 'DBA_Profile'
		, @subject= @object 
		, @recipients = '[email protected]'
		, @body_format='html'
		, @body = @HTML
	
		FETCH NEXT FROM category_cursor INTO @error_category;
	END
	CLOSE category_cursor  
	DEALLOCATE category_cursor 
end

 

Email examples

Here are some emails sent by this Job.

On SQL Server start:On Failed login:

Conclusion

The drawback of this approach is that I have to know exactly what I want to get emailed about. If I don’t use Agent Alerts I might miss some critical errors.
What is nice is that I can look for informational messages and not only errors.
I hope you found this interesting and it gave you some ideas on how to get information from your SQL Server instances.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Steven Naudet
Steven Naudet

Consultant