Script to get SQL Server Error logs via Email

This is one of the handy script to get sql server error logs via email.

declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();

create table #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(max) )

insert #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

create table #SQL_Log_Errors (
[logdate] datetime,
[Message] varchar (500) )

insert into #SQL_Log_Errors
select LogDate, Message FROM #ErrorLog
where (Message like '%err%'
or Message like '%warn%'
or Message like '%kill%'
or Message like '%dead%'
or Message like '%cannot%'
or Message like '%could%'
or Message like '%fail%'
or Message like '%not%'
or Message like '%stop%'
or Message like '%terminate%'
or Message like '%bypass%'
or Message like '%roll%'
or Message like '%truncate%'
or Message like '%upgrade%'
or Message like '%victim%'
or Message like '%recover%'
or Message like '%critical%'
or Message like '%IO requests taking longer than%')
AND Message not like '%errorlog%'
AND Message not like '%dbcc%'
AND Message not like '%checkdb%'
order by logdate desc

drop table #ErrorLog

declare @cnt int
select @cnt=COUNT(1) from #SQL_Log_Errors
if (@cnt > 0)
begin

declare @strsubject varchar(100)
declare @oper_email nvarchar(100)

set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
select @strsubject='There are errors in the SQL Error Log on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>SQL Error Log Errors - ' + @@SERVERNAME + '</H1>' +
N'
<table border="1">' +
N'
<tr>
<th>Log Date</th>
' +
N'
<th>Message</th>
</tr>
' +
CAST ( ( SELECT td = [logdate], '',
td = [Message]
FROM #SQL_Log_Errors
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
' ;

EXEC msdb.dbo.sp_send_dbmail
@from_address='',
@recipients= @oper_email,
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='YourProfileName'

end

drop table #SQL_Log_Errors

go

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter