· 8 years ago · Jan 10, 2018, 10:40 PM
1/***************************************************************************************************
2Proc to send email or return a table results of the error log and agent for the past X hours.
3
4We only check the last 2 agent logs... we check all error logs which meet the @hoursBack
5threshold provided in case there were multiple restarts of SQL Server or sp_cycle_errorlog
6is being used and the hours back would be spread across multiple files.
7
82018-01-10 S.Simon Created Proc
9***************************************************************************************************/
10
11create procedure usp_error_log(
12 @emailResults bit = 0 --1 sends an email to @email or @operator. @operator overrides @email
13 ,@operator varchar(256) = null --name of operator set up in DB. Will find email for this operator
14 ,@email varchar(256) = null --email address you want results sent to
15 ,@hoursBack int --number of hours back you want to check the error log. Searches last two error logs
16 ,@logTextExclusionsStr varchar(max) --semicolon seperated string of log text exclusions
17 ,@processInfoExclusionsStr varchar(max) --semicolon seperated string of process info exclusions
18 )
19as
20
21
22
23-----------------------------------------------------------------------------------
24----proc parameters for testing
25-----------------------------------------------------------------------------------
26--declare @emailResults bit = 0
27--declare @operator varchar(256) = null
28--declare @email varchar(256) = null
29--declare @hoursBack int = 36
30--declare @logTextExclusionsStr varchar(max) = ('is complete;login;found 0 errors;finished without errors;informational message;backup database successfully')
31--declare @processInfoExclusionsStr varchar(max) = ('logon')
32
33
34
35
36---------------------------------------------------------------------------------
37--Create Jeff Moden's splitter function if it doesn't exist
38--and the DB isn't read_only
39--and the DB isn't a ROR in a HADR (if it's online)
40
41--i am sure i missed other checks that need to be implemented here
42---------------------------------------------------------------------------------
43
44--function exists?
45IF NOT EXISTS (SELECT *
46 FROM sys.objects
47 WHERE object_id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]')
48 AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
49
50AND
51
52--db is read_only?
53(SELECT is_read_only
54FROM sys.databases
55WHERE name = (SELECT DB_NAME())) = 0
56
57AND
58
59--primary replica, or if NULL it's not a HADR
60isnull(
61 (select
62 ars.role
63 --,ars.role_desc
64 --,ars.operational_state_desc
65 --,dbc.database_name
66 --,r.replica_server_name
67 from sys.dm_hadr_availability_replica_states ars
68 inner join sys.availability_replicas r
69 on r.replica_id = ars.replica_id
70 inner join sys.availability_databases_cluster dbc
71 on dbc.group_id = ars.group_id
72 where
73 r.replica_server_name = @@SERVERNAME
74 and dbc.database_name = DB_NAME()
75 and ars.operational_state_desc = 'ONLINE'),1) = 1
76
77BEGIN
78
79declare @sql varchar(max)
80set @sql = '
81
82CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
83--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
84
85RETURNS TABLE WITH SCHEMABINDING AS
86RETURN
87
88/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
89enough to cover VARCHAR(8000)*/
90
91 WITH E1(N) AS (
92 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
93 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
94 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
95 ), --10E+1 or 10 rows
96 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
97 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
98 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
99 -- for both a performance gain and prevention of accidental "overruns"
100 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
101 ),
102cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
103 SELECT 1 UNION ALL
104 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
105 ),
106cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
107 SELECT s.N1,
108 ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
109 FROM cteStart s
110 )
111--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
112 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
113 Item = SUBSTRING(@pString, l.N1, l.L1)
114 FROM cteLen l
115'
116exec(@sql)
117END
118
119ELSE
120
121--if the function still doesn't exist, then DB is read only, read only replica, or something else perhaps
122BEGIN
123 IF NOT EXISTS (SELECT *
124 FROM sys.objects
125 WHERE object_id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]')
126 AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
127 begin
128 raiserror('[dbo].[DelimitedSplit8K] Split Function is not installed on this instance',16,1)
129 return
130 end
131END
132
133
134
135---------------------------------------------------------------------------------
136--create tables to be used as exclusions
137--populate with the comma seperated parameters
138---------------------------------------------------------------------------------
139
140declare @logTextExclusions table (LogText varchar(4000))
141insert into @logTextExclusions
142select Item from dbo.DelimitedSplit8K(@logTextExclusionsStr,';')
143
144
145declare @processInfoExclusions table (ProcessInfo varchar(256))
146insert into @processInfoExclusions
147select Item from dbo.DelimitedSplit8K(@processInfoExclusionsStr,';')
148
149
150---------------------------------------------------------------------------------
151--set @hours back to a negative number, so we can go back in time
152---------------------------------------------------------------------------------
153set @hoursBack = (@hoursBack * -1)
154declare @startDate datetime = dateadd(hour, @hoursBack, getdate())
155
156
157---------------------------------------------------------------------------------
158--handle case sensitivitiy just in case
159--coalation should be handled / customized if need be
160---------------------------------------------------------------------------------
161
162update @logTextExclusions
163set LogText = lower(LogText)
164
165update @processInfoExclusions
166set ProcessInfo = lower(ProcessInfo)
167
168
169---------------------------------------------------------------------------------
170--get @operator email if the param was used, otherwise use @email
171---------------------------------------------------------------------------------
172
173if(@operator is null and @email is null and @emailResults = 1)
174begin
175 raiserror('Email or Operator is required when @emailResults = 1',16,1)
176 return
177end
178else
179begin
180 declare @operator_email varchar(256) = coalesce(@email,(select email_address from msdb..sysoperators where upper([name]) = upper(@operator)))
181
182 if (@operator_email is null and @emailResults = 1)
183 begin
184 declare @errMsg varchar(600) = 'No email address set for operator passed in' + @operator
185 raiserror(@errMsg,16,1)
186 return
187 end
188end
189
190
191
192---------------------------------------------------------------------------------
193--variables used for emailing results
194---------------------------------------------------------------------------------
195if @emailResults = 1
196begin
197 declare @error_xml nvarchar(max)
198 declare @error_body nvarchar(max)
199
200 declare @agent_xml nvarchar(max)
201 declare @agent_body nvarchar(max)
202
203
204 declare @server varchar(256) = (select @@SERVERNAME)
205 declare @subject varchar(1024)
206end
207
208---------------------------------------------------------------------------------
209--tables to hold error log results
210---------------------------------------------------------------------------------
211
212if object_id('tempdb..##errorLog') is not null drop table ##errorLog
213
214create table ##errorLog(LogDate datetime2
215 ,ProcessInfo varchar(64)
216 ,LogText varchar(max))
217
218if object_id('tempdb..#agentLog') is not null drop table #agentLog
219
220create table #agentLog(LogDate datetime2
221 ,ProcessInfo varchar(64)
222 ,LogText varchar(max))
223
224
225
226
227---------------------------------------------------------------------------------
228--get errors from error log going back to @startDate
229--we cycle through the error logs in case there were multiple restarts
230--or multiple sp_cycle_errorlog for the time frame
231---------------------------------------------------------------------------------
232
233
234--find the number of error logs there currently are. Credit: https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html
235DECLARE @FileList AS TABLE (
236 subdirectory NVARCHAR(4000) NOT NULL
237 ,DEPTH BIGINT NOT NULL
238 ,[FILE] BIGINT NOT NULL
239 );
240
241DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);
242SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));
243SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'', REVERSE(@ErrorLog))) + N'';
244
245INSERT INTO @FileList
246EXEC xp_dirtree @ErrorLogPath, 0, 1;
247
248DECLARE @NumberOfLogfiles INT;
249SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');
250
251
252--params for loop
253declare @i int = 0
254declare @maxDate datetime = (select max(isnull(LogDate,'19010101')) from ##errorLog)
255
256
257while (@i < @NumberOfLogfiles or @maxDate < @startDate)
258begin
259set @sql = '
260insert into ##errorLog
261exec master.dbo.xp_readerrorlog
262 ' + cast(@i as char(1)) + ' --log number
263 ,1 --Error Log, not Agent
264 ," "
265 ," "
266 ,''' + convert(varchar(8),@startDate,112) + ''' --Date filter
267 ,null
268 ,"desc"
269 '
270 exec (@sql)
271 set @i = @i + 1 --iterate the error log number
272 set @maxDate = (select max(isnull(LogDate,'19010101')) from ##errorLog) --reassign the current maxdate
273end
274
275
276
277---------------------------------------------------------------------------------
278--get errors from current agent log going back to @startDate
279--still uses @startDate param
280---------------------------------------------------------------------------------
281insert into #agentLog
282exec master.dbo.xp_readerrorlog
283 0
284 ,2
285 ," "
286 ," "
287 ,@startDate
288 ,null
289 ,"desc"
290
291---------------------------------------------------------------------------------
292--get errors from previous agent log in case a restart happened
293--still uses @startDate param
294---------------------------------------------------------------------------------
295insert into #agentLog
296exec master.dbo.xp_readerrorlog
297 1
298 ,2
299 ," "
300 ," "
301 ,@startDate
302 ,null
303 ,"desc"
304
305
306---------------------------------------------------------------------------------
307--return the error log results, while removing some noise
308---------------------------------------------------------------------------------
309
310if @emailResults = 0
311begin
312 select
313 el.LogDate
314 ,el.ProcessInfo
315 ,el.LogText
316 from
317 ##errorLog el
318 left join
319 @logTextExclusions ex on
320 lower(el.LogText) like '%' + ex.LogText + '%'
321 left join
322 @processInfoExclusions pie on
323 lower(el.ProcessInfo) = pie.ProcessInfo
324 where
325 ex.LogText is null
326 and pie.ProcessInfo is null
327 and LogDate >= @startDate
328 order by
329 el.LogDate desc
330 ,case when el.LogText like 'Error:%' then 1 else 2 end --places the error number above it's associated message
331end
332
333else
334
335begin
336 if(select count(*)
337 from
338 ##errorLog el
339 left join
340 @logTextExclusions ex on
341 lower(el.LogText) like '%' + ex.LogText + '%'
342 left join
343 @processInfoExclusions pie on
344 lower(el.ProcessInfo) = pie.ProcessInfo
345 where
346 ex.LogText is null
347 and pie.ProcessInfo is null
348 and LogDate >= @startDate) > 0
349
350 begin
351 set @error_xml = cast(( select el.LogDate as 'td', '', el.ProcessInfo as 'td', '', el.LogText as 'td'
352 from
353 ##errorLog el
354 left join
355 @logTextExclusions ex on
356 lower(el.LogText) like '%' + ex.LogText + '%'
357 left join
358 @processInfoExclusions pie on
359 lower(el.ProcessInfo) = pie.ProcessInfo
360 where
361 ex.LogText is null
362 and pie.ProcessInfo is null
363 and LogDate >= @startDate
364 for xml path('tr'), elements ) as nvarchar(max))
365
366 set @error_body = '<html><body><H3>SQL Error Log Report</H3>
367 <table border = 1>
368 <tr>
369 <th>LogDate</th> <th>ProcessInfo</th> <th>LogText</th>'
370
371 set @error_body = @error_body + @error_xml + '</table></body><html>'
372
373 set @subject = @server + ' Error Log Report'
374
375 exec msdb..sp_send_dbmail
376 @profile_name = 'MyProfile'
377 ,@recipients = @operator_email
378 ,@subject = @subject
379 ,@body = @error_body
380 ,@body_format = 'HTML'
381 end
382end
383
384---------------------------------------------------------------------------------
385--return the agnet log results
386---------------------------------------------------------------------------------
387
388
389if @emailResults = 0
390begin
391 select *
392 from #agentLog
393 where LogDate >= @startDate
394 order by LogDate desc
395end
396
397else
398
399begin
400 if(select count(*) from #agentLog where LogDate >= @startDate) > 0
401 begin
402 set @error_xml = cast(( select [LogDate] as 'td', '', [ProcessInfo] as 'td', '', [LogText] as 'td'
403 from #agentLog
404 where LogDate >= @startDate
405 for xml path('tr'), elements ) as nvarchar(max))
406
407 set @error_body = '<html><body><H3>SQL Agent Log Report</H3>
408 <table border = 1>
409 <tr>
410 <th>LogDate</th> <th>ProcessInfo</th> <th>LogText</th>'
411
412 set @error_body = @error_body + @error_xml + '</table></body><html>'
413
414 set @subject = @server + ' Agent Log Report'
415
416 exec msdb..sp_send_dbmail
417 @profile_name = 'MyProfile'
418 ,@recipients = @operator_email
419 ,@subject = @subject
420 ,@body = @error_body
421 ,@body_format = 'HTML'
422 end
423end