In my previous article titled How to centralize your SQL Server Event Logs, I discussed how to forward all your event logs into a single table within a database. I called the database DBA and the table was called EventLogStaging.

This is the follow up article how we can take the information contained within that table and alert us in real time to errors that occur.

Why do this

The main reason I do this is for simplicity. While it may seem easier to create individual alerts for errors you are interested in, I can assure you that with this method, nothing is left to chance. When we create a SQL Agent job for example, we have to provide alerting details for each and every job we create. We must also set up Database Mail if we want those alerts to come to us via email. For every server we monitor for every job we create, we must do this.

With my method, I simply use the process that is already in place described in the first article and expand on it to create alerts based on triggers. Of course, you must follow the previous article step by step to make use of this functionality. We must also define what we want to be alerted on by writing errors to the Windows Application Log.

Let's get started

The first thing we have to do is create another table. We will call this table EventLog and it will be used to hold only events we want from the EventLogStaging table. Remember that the previous article put EVERYTHING into the EventLogStaging table. We use this new table to only hold 1 event per server per hour.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog](
  [RecordNumber] [int] NOT NULL,
  [Category] [int] NOT NULL,
  [ComputerName] [varchar](250) NOT NULL,
  [EventCode] [int] NOT NULL,
  [EventType] [int] NOT NULL,
  [Message] [varchar](2000) NULL,
  [SourceName] [varchar](250) NOT NULL,
  [TimeGenerated] [datetime] NULL,
  [TimeWritten] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

The next thing we must do is create a trigger and a stored procedure. The first is a trigger on the EventLogStaging table. Remember the EventLogStaging table was created in the previous article.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER InsertToEventLog ON [dbo].[EventLogStaging]
 for INSERT
AS
-- Get the information from the Inserted virtual table
declare @RecordNumber int
declare @Category int
declare @ComputerName varchar(250)
declare @EventCode int
declare @EventType int
declare @Message nvarchar(4000)
declare @SourceName varchar(250)
declare @TimeGenerated datetime
declare @TimeWritten datetime
select 
    @RecordNumber = RecordNumber
  , @Category = Category
  , @ComputerName = ComputerName
  , @EventCode = EventCode
  , @EventType = EventType
  , @Message = Message
  , @SourceName = SourceName
  , @TimeGenerated = TimeGenerated
  , @TimeWritten = TimeWritten
 from inserted
if(@@rowcount = 1) -- Do one row only 
 exec DBA.dbo.EventLogStagingInsertTrigger @RecordNumber, @Category
    , @ComputerName, @EventCode, @EventType, @Message, @SourceName
    , @TimeGenerated, @TimeWritten
else
 raiserror('Error occurred when inserting rows from EventLogStaging table to EventLog table', 16, 1) with log

The above trigger creation will tell you

The module 'InsertToEventLog' depends on the missing object 'DBA.dbo.EventLogStagingInsertTrigger'. The module will still be created; however, it cannot run successfully until the object exists.

But that is ok, because we will create the stored procedure in the next step.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc EventLogStagingInsertTrigger 
    @RecordNumber int, @Category int, @ComputerName varchar(250),
   @EventCode int, @EventType int, @Message varchar(2000), 
   @SourceName varchar(30), @TimeGenerated datetime, @TimeWritten datetime
As
If NOT exists(
 select ComputerName, EventType, SourceName
  from EventLog 
  where (ComputerName = @ComputerName 
     and EventType = @EventType 
     and SourceName = @SourceName)
 )
 Begin
  Insert into EventLog values 
   (@RecordNumber, @Category, @ComputerName, @EventCode, @EventType,
    @Message, @SourceName, @TimeGenerated, @TimeWritten)
 End
-- If Record exists, check if the record has expired, if it does, insert a new one thus trigger a new alert
 Else 
 Begin
  declare @startTime DateTime
  Begin
   select @startTime = max(TimeWritten) 
    from EventLog 
    where (ComputerName = @ComputerName 
        and EventType = @EventType 
        and SourceName = @SourceName 
        and Message = @Message
    )
  End
  If(((DateDiff(minute, @startTime, @TimeWritten))> 60) Or @startTime is null) 
  -- If the message doesn't exist, @startTime would be NULL
   Begin
    Insert into EventLog values 
     (@RecordNumber, @Category, @ComputerName, @EventCode, @EventType,
      @Message, @SourceName, @TimeGenerated, @TimeWritten)
   End
End

The stored procedure above has one main goal. That goal is to insert records into the table EventLog. It will only write data into the EventLog table that is older than 60 minutes or completely new. It will compare by the TimeWritten column to first see if a record exists that is older than 60 minutes. We do this so we are not spammed for multiple alerts that are the same. The Windows Event Log may write 5 messages that are the same within an hour's time, but we will only use the first one it encounters once per hour.

Create a trigger and stored procedure to send mail

Lets first create a trigger in our new table EventLog. I call this trigger SendEmailorPage since that is what is ultimately does.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER SendEmailOrPage ON [dbo].[EventLog]
FOR INSERT
AS
-- Get the information from the Inserted virtual table
declare @RecordNumber int
declare @Category int
declare @ComputerName nvarchar(250)
declare @EventCode int
declare @EventType int
declare @Message nvarchar(4000)
declare @SourceName nvarchar(250)
declare @TimeGenerated datetime
declare @TimeWritten datetime

select @RecordNumber = RecordNumber, @Category = Category, @ComputerName = ComputerName, @EventCode = EventCode,
@EventType = EventType, @Message = Message, @SourceName = SourceName, @TimeGenerated = TimeGenerated,
@TimeWritten = TimeWritten
from inserted
where Message IS NOT NULL
if(@@rowcount = 1) -- Do one row only 
exec DBA.dbo.EventLogInsertTrigger @RecordNumber, @ComputerName, @Message, @TimeGenerated
;

You will receive another message from SQL Server Management Studio telling you

The module 'SendEmailOrPage' depends on the missing object 'DBA.dbo.EventLogInsertTrigger'. The module will still be created; however, it cannot run successfully until the object exists.

Not to worry. We will create the stored procedure next.

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc EventlogInsertTrigger 
  @RecordNumber int, @ComputerName varchar(50), @Message varchar(1000), @TimeGenerated datetime
As
Begin
DECLARE @HtmlMessageBody NVARCHAR(MAX)
declare @SevIndex int
declare @sevStr char(2)
declare @sev int
declare @sub nvarchar(250)
declare @toEmail varchar(500)
declare @now datetime
declare @ServerName nvarchar(250)
select @now = getdate()
set @ServerName = @ComputerName
select @toEmail = 'this_is_your_email_Address@your_domain.com' --- CHANGE THIS. USE YOUR EMAIL ADDRESS!!!!
------------------------------------------------------------------------------------
SELECT @HtmlMessageBody =
N'<H1>Server '+@ServerName+' is reporting an error</H1>' --Large Bold "Heading 1" above the table
+ N'<table border="1" cellspacing="0">' --Start the table tag and make the grid "pretty"
+ N'<tr bgcolor="Red">' --Start a header row with a "pretty" background color
+ N' <th>ERROR</th>' -- This is a "th" or Table Header for a column
+ N'</tr>' --Finish the header row.
+ CAST(--==== This builds and concatenates each row into the HTML
 ( 
 SELECT @Message
 FOR XML PATH('tr'), TYPE --This does the concatenation and de-entization of each row in HTML <tr></tr> tags
 ) AS NVARCHAR(MAX) --Must absolutely convert the resulting XML to NVARCHAR(MAX)
)
+ N'</table>' --Finishes up the table tag
;
------------------------------------------------------------------------------------
BEGIN
select @SevIndex = patindex('%Severity%', @Message)
select @sevStr = substring(@Message, @SevIndex + 10 , 2) 
select @sev = cast(@sevStr As tinyint)
select @sub = 'Error ' + @sevStr + ' occurred on computer ' + @ServerName
Begin 
 EXEC msdb.dbo.sp_send_dbmail @recipients = @toEmail,@subject = @sub, @body = @HtmlMessageBody, @body_format = 'HTML'
End
END
END

It may seem like over kill, but I like my emails pretty. Don't forget to set your email address in the @toEmail variable or you will never receive an email.

Remember when I said it was a hassle setting up DB Mail on the servers we want message from? Well, you have to set it up on the server that the database DBA is on. The stored procedure EventLogInsertTrigger needs it. So make sure you have DB Mail configured and tested before you start trying to use this.

Testing

So you want to see it in action, right? No problem. Let's just create a dummy record into the table EventLogStaging and see what happens.

USE [DBA]
GO
INSERT INTO [dbo].[EventLogStaging]
 ([RecordNumber]
 ,[Category]
 ,[ComputerName]
 ,[EventCode]
 ,[EventType]
 ,[Message]
 ,[SourceName]
 ,[TimeGenerated]
 ,[TimeWritten])
VALUES
 ('123'
 ,'2'
 ,'TEST_SERVER_123'
 ,'17063'
 ,'1'
 ,'Error: 50000 Severity: 17 State: 1 Something REALLY BAD HAS HAPPENED!!!!'
 ,'MSSQLSERVER'
 ,'2012-04-01 10:10:00.000'
 ,'2012-04-01 10:10:00.000'
 )
GO

Did you receive an email? It should look something like this.

Notice that if you run the insert statement again, you will not receive an email. If you try again in an hour, you will.

Overview - What is happening here

In my first article, I described how we can use the SQL Server Agent to 'forward' SQL Server events to a single SQL Server instance. The server we use to 'receive' those events has a small Visual Basic Script running on it that parses the Windows Event Logs into a table called EventLogStaging.

When a record gets inserted into the EventLogStaging, a trigger is fired. It takes the inserted records and IF the record does not exist in the table EventLog within the last 60 minutes, it inserts it. Once it is in the EventLog table another trigger is fired. That trigger calls a stored procedure that sends an email. So basically, everything that ever goes into the table EventLog will always send an email.

The errors that will eventual make it into the EventLog table and send emails are anything SQL logs in the Windows Event Log. Of course, the informational messages will be in the Windows Event Log, but the Visual Basic Script in the first article only parses the Errors. Some of you may ask, "What errors?". It will eventually be the errors you decide to write to the Windows Application Log. We do this with the RAISERROR function using the option 'with log'. Anything you can think of to monitor within SQL can be used to create a Windows Application Log Error thus sending an email.

What's next

In my next article, I will expand on this functionality to show how we can get emails for some very useful things. At my current employer, I use the methods I described above to receive alerts for Failed Logins, Failed SQL Server Agent Jobs, CPU spikes, Long Running SQL Server Agent jobs, Down SQL Server, Down SQL Server Agent and low disk space warnings, all from a single server. Just remember, that if you can make use of RAISERROR ‘with log’, you can figure out some pretty neat stuff for yourself. Stay tuned........