Automate SQL Server error log alerting with Minion Enterprise

Articles, Minion Enterprise, Tech / By

Minion Enterprise (ME) is our tool for auditing, managing, and alerting on SQL Server instances and servers. With ME, you can automate custom SQL Server error log searches!​ One of the features is the error log search alert.

The Error Log Search module allows you to set up specific error log search terms in the dbo.ErrorLogSearch table.  This module automatically gathers any search term matches, and logs them in the Collector.ErrorLog table for alerting or reporting.

We can set up an automated error log search for any term we choose, and receive an email when any of those terms show up in a SQL Server error log, on any managed instance.

With a fresh installation of Minion Enterprise (ME), we would follow the following steps:

  1. Set up a DBA email in ME, to receive email alerts.
  2. Input the error log search terms
  3. Create and schedule the alert procedure

1. Set up DBA email

Check the dbo.EmailNotification table for a “DBA” email entry. If one does not exist, insert a row with the appropriate email address:

IF NOT EXISTS (select * from dbo.EmailNotification WHERE Comment='DBA')
 INSERT INTO dbo.EmailNotification (EmailAddress, Comment)
 VALUES ('DBAs@email.com', 'DBA');

SELECT * FROM EmailNotification;
 

2. Input error log search terms

Let’s say that we want to monitor for database corruption. (Note: This is a very important – no, critical! – DBA task.)  Corruption alerts show up in the error log as “Error: 823” or “Error: 824” messages whenever a DBCC CHECKDB or DBCC CHECKTABLE operation finds corruption.

To configure Minion Enterprise (ME) to search for and log these terms, we use the following code on the ME repository server:

INSERT INTO [Minion].dbo.ErrorLogSearch ([LogNumber]
 , [LogType]
 , [Class]
 , [Search1]
 , [SortOrder]
 , [IsActive]
 , [Comment]) 

SELECT 0 AS [LogNumber]
 , 1 AS [LogType]
 , 'Corruption' AS [Class]
 , 'Error: 823' AS [Search1]
 , 'DESC' AS [SortOrder]
 , 1 AS [IsActive]
 , 'High priority: Standard corruption search for all servers.' AS [Comment]

UNION ALL

SELECT 0 AS [LogNumber]
 , 1 AS [LogType]
 , 'Corruption' AS [Class]
 , 'Error: 824' AS [Search1]
 , 'DESC' AS [SortOrder]
 , 1 AS [IsActive]
, 'High priority: Standard corruption search for all servers.' AS [Comment];

3. Create and schedule the alert procedure

The MinionWare Community Zone provides the Alert on found error log search terms​.

Download and install this stored procedure (CustomAlert.ErrorLogSearch), then create a job for each service level (Gold, Silver, Bronze) to runs the stored procedure regularly:

EXEC CustomAlert.ErrorLogSearch @ServiceLevel=''Gold'', @SearchHistoryHrs=9;

Remember, email alerts are sent to the “DBA” email configured in the dbo.EmailNotification table.

For more information, see the MinionWare support page on the error log search alert.