Introduction

The Error Logs of your SQL instances often contain valuable information. Information that may require follow-up attention or an awareness of certain situations. Information such as backups failing, I/O problems, or failed login attempts.

This information, however, is usually buried among a multitude of everyday messages that can be ignored. How do you filter out only the relevant messages and receive them in a single mail notification for all your SQL instances? This PowerShell solution will do just that.

Setup

The script presented below can run on any given server in your shop as long as it has a SQL Instance running on it. We will refer to this server as the management server below. It can be run manually, but will normally be scheduled as a Windows Scheduled Task. This script works as follows.

Using a text file with SQL Server instance names, the script will read all the error log entries of each of your SQL instances during a specific interval and transfer these entries to a holding table on the management server.

Next, the error log entries are filtered because many of them are informational or can be ‘expected’ in your environment. You only want to be warned about the unexpected ones. Therefore, the entries retrieved are compared against an ‘exclusion table’. This is a table with (parts of) messages you want to exclude. You can define general exclusions (exclude on all servers, at any time) or specific ones (only on server X, or only on server Y between 02:15 and 04:30). By default, all failed login messages are also preserved.

Once filtering is complete, two emails are sent. One with the details of all failed logins, if any were found and another showing you all remaining error log lines.

Examples of both are shown below:

In case we end up with a great many error log entries after filtering (this can happen after an upgrade, for example), the HTML mail would become too bulky. For that reason, if the number of messages exceeds 2000, a mail is sent warning you about this situation. The email will invite you to check the records directly in the holding table on the management server (e.g. from within SSMS). The same holds true for a very large number of failed logins.

An example of this email is shown below.

Setup and Requirements

First, decide on a SQL Server instance to use as management server. On this instance, create a small database to store the error log entries which persist after filtering. The specs for this database are:

• Default name = SQLErrorLogMonitor
• Size will depend on the number of SQL instances, the number of ‘non-standard’ messages you receive and the number of days you want to retain your results (this is configurable)
• 50 MB + autogrowth enabled is a nice starting point.

Next, configure SQL Database Mail on the management server. Then make sure the management server has SQL access to all the SQL instances you want to monitor. Finally, make sure the user running the script has permission to run the (undocumented) extended procedure, xp_readerrorlog, on all the SQL instances you want to monitor.

Installation Walkthrough

To set up this process, first create a directory on the management server in which to place the scripts. Navigate to that directory and copy the scripts (7 sql files, 1 txt file, 1 ps file) to this location.

Open the PS script Monitor_SQL_Errorlog.ps1 in an editor. Modify the parameters in the section entitled “USER DEFINED PARAMETERS” to reflect your local setup.

Open the text file SQLinstances.txt and add a few of your SQL instance names for an initial test.

Open the script 7_Populate_SQLErrorlog_exlude_table.sql. This will populate your ‘exclusion table’. If you do not want to start with the defaults suggested, feel free to remove any entries or add new ones relevant to your setup.

Execute the SQL scripts numbered 1-7, in that order, against the database on the management server. This will set up the database on the management server to collect the information.

Open a PowerShell command window, make sure the Execution Policy will allow you to run the script and run the PS script, Monitor_SQL_Errorlog.ps1.  After the script has completed, check whether the tables ProgressMonitor and SQLErrorLog in your management server database contain records.

Check whether mail notification(s) is/are coming in.

Usage

You can run the script with an interval of your choice. For each SQL instance, the script logs the date/time of the last error log item retrieved and will pick up from there. If the script runs for the first time against a given instance, it will retrieve the error messages written during the last 24 hours.

Scheduling

The way I run this is to schedule this is with the Windows Task Scheduler. You can, however, use any scheduler that allows you to execute a PowerShell script.

This example presumes you have installed the scripts in D:\SQLErrorlogMonitor.

Open Windows Task Scheduler. In the Actions menu on the right, choose Create Task. In the General tab, provide a name for this task, e.g. Check SQL Error Logs. Under Security Options, specify the credentials to run the script (this account must have sufficient privileges in SQL). Select the bullet, Run whether user is logged on or not.

In the Triggers tab, click New and create the desired schedule.

In the Actions tab, click New and set Action to Start a program. Under Program/script type powershell.exe. Under Add arguments type (on one line)

-Noninteractive & {D:\SQLErrorlogMonitor\Monitor_SQL_Errorlog.ps1}

Press OK twice to save the task. Test the script by starting it manually in the Task Scheduler.

That's it, now you have a way to get alerted to information in the error log that is important in your environment.