xp_readerrorlog 自动发错误预警

parameters:
  1.Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2.Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3.Search string 1: String one you want to search for
  4.Search string 2: String two you want to search for to further refine the results
  5.Search the start time
  6.Search the end time
  7.Sort order for results: N'asc' = ascending, N'desc' = descending

 

Codes:

   set nocount on

   set quoted_identifier off

  

   declare @hostname varchar(30)

   set @hostname = HOST_NAME()

  

   DECLARE @tableHTML  NVARCHAR(MAX)

   declare @fullerrlog table(logdate datetime,proinfo varchar(25), descrip varchar(1000))

   declare @Time_Start datetime;

   declare @Time_End datetime;

   set @time_start=dateadd(mi,-32,getdate());

   set @Time_End=getdate();

   insert @fullerrlog

   EXEC master.dbo.xp_readerrorlog 0, 1, 'error', NULL, @Time_Start, @Time_End

   insert @fullerrlog

   EXEC master.dbo.xp_readerrorlog 0, 1, 'memory pressure', NULL, @Time_Start, @Time_End

  

  if exists(select Logdate as 'DATE', left(descrip,100) as 'Description' from @fullerrlog) 

   begin

   SET @tableHTML =

       N'<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>

       <H1>'

   set @tableHTML = @tableHTML + @hostname + ' sqlserver 错误报告</H1>

       <table border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid">

       <tr><th>DATE</th><th>Description</th></tr>' +

       CAST (( SELECT

                  td = convert(varchar(28),Logdate,121),'',

                    td = left(descrip,100)

                  from @fullerrlog      

              FOR XML PATH( 'tr')

       ) AS NVARCHAR(MAX) ) +

       N'</table><br>'

 

    declare @subjects varchar(200)

    set @subjects = @hostname + ' Error报告'

       EXEC msdb.dbo.sp_send_dbmail @recipients='jerrynet@XX.com',

       @subject = @subjects,

       @body = @tableHTML,

       @body_format = 'HTML' ;

   end