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.

1
2
3
4
5
6
7
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.

1
2
3
4
5
6
7
8
9
10
11
12
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.

1
2
insert into @errorlog
    exec sp_readerrorlog 0

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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
--  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, -@CheckPeriodInMinute, 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 @HTML=@HTML_header+'<h1>'+@error_category+' (last '+convert(varchar(50),@CheckPeriodInMinute)+' min)</h1>'
        
        SELECT @HTML=@HTML+'
        <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 @HTML=@HTML+@HTML_part+'
            '
            FETCH NEXT FROM error_cursor INTO @logdate, @processInfo, @Text;
        END
        CLOSE error_cursor 
        DEALLOCATE error_cursor
        
        -- HTML Table end + send email
        set @HTML=@HTML+'</table>
        '+@HTML_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.

Written by Steven Naudet

Post Views: 4,302
Thumbnail [60x60]
by
Steven Naudet