The SQL Server error log has a wealth of information contained inside.
Sometimes it is too much, sometimes it is not enough. And then situations occur
when you do not know if there is enough in the log or not until well after you
have a resolution. In my opinion, things are even more complex with 2005, as
there seems to be a lot of messages that end with "This is an
informational message only; no user action is required." If that is the
case, then I may or may not want to be alerted about this, right? User action
may not be "required", but what if it is actually
Like many shops, we developed an in-house system for monitoring our database
servers. Built over the past two years, the system is called DBA_Perform. One of the very first items we incorporated
into DBA_Perform was the error log scan. Code for all
of the tables and stored procedures for the solution can be found in the
downloadable zip files. There is also a readme file
included to assist with your implementation. I have tried to keep things as
general as possible, but some additional modifications may be necessary based
upon your specific environment.
The Need To Know
I believe that many shops have processes in place to scan the logs for
review. Some shops are small enough (less than ten total servers) that no
process is really necessary because they can simply review the logs by hand as
needed. However, I would argue that using an automated process to scan the log
periodically is the smart thing to do, so that you can be alerted should
something occur. That is exactly what we set out to build over two years ago.
What we found was that setting up a process to scan the error log is very
easy to do. There were lots of examples available from community sites. We
sampled some and found that while they did work, there was always something
missing. What one solution would have, another solution would not. So, we took
the best of everything we could find, added in our own ideas, and ended up with
the solution being presented. What set ours apart from others at the time was
the ability to include (or exclude) certain keywords that would appear in the
This was very important for our group as the number of servers we managed
went from around ten to over seventy in less than fifteen months. The
combination of production, test, and development servers also meant that the
errors we would be concerned about varied. For example, our production servers
are set to log any failed logins, but test and development are not. Any solution
we would roll out to a new server had to work regardless of environment. But do
we want to be alerted by email each and every time someone fails to log in? No,
probably not. But what if that failed attempt was by someone trying to login as
"sa", or "admin", or
"root"? That could be something that we want to know about quickly,
as it could be an attack or a virus.
First, get the code. This has been tested with SQL Server 2000 and SQL Server 2005.
We needed to build a process that would scan and dump the contents of the
error log, clean up the contents based upon keywords we wanted to include
and/or exclude, and then send us an email if necessary. The procedure we
currently use is named Usp_error log_Notification,
and is included the zip files. The procedure makes use of the xp_readerror log extended stored procedure in order to
gather all details from the log into a temporary table as an initial starting
point. The process does a check to see what version of MS SQL Server you are
running, because Microsoft changed the output of the xp_readerrorlog
extended stored procedure in SQL 2005.
While checking the version, we also verify that the reading of the error log
results in a valid date that will ultimately be inserted before our emails are
sent. This is essential because the very next step does a calculation of the
maximum date, so we need to make certain the date is valid in order for the max() function to return a proper result. If this is the
first time a scan has been run, then a default date is assigned. Otherwise, it
will retrieve the max date from an earlier run of the process from the error
tables and that date will be used to filter out rows from the temporary tables
later in the process.
Before that cleanup happens, a cursor is built to go through the entries in
the temporary table and filter out the rows that do not have a keyword that we
want to scan for. The keywords for this part are found in the ErrorlogScanKeyword table, and include words and phrases
such as 'stack', 'is full', and 'error'. If a row exists in the #Errors
temporary table that do not have any of the words
listed in this table, then they are filtered out.
After filtering the temporary table, leaving behind only rows that have
keywords we are scanning for, we then cleanup the table by using the calculated
date mentioned in the previous paragraph. As our #Errors table gets smaller, we
next need to force the inclusion of specific words. We take the current rows in
#Errors and insert them into a separate temporary table named #ErrorsHOLD. At this point we remove rows from #ErrorsHOLD that do not have words and phrases found in the ErrorlogForceInclude table. In our experience, we found
that is not sufficient to only exclude certain words, there also needs to be
the forced inclusion of keywords as well. By forcing the inclusion of certain
keywords (for example, the word 'admin' would be a forced include), we can tell
the system to be more flexible. This is the magic. We can tell the system to
not bother us every time the phrase "Login failed for user", but to
make certain we know whenever "Login failed for user 'sa'"
appears in the error log.
After setting those rows off to the side, we do one final scan on the #Errors
temporary table to exclude certain keywords outright (for example, certain
error numbers above 50000, and this is where we would exclude the generic
'Login failed for user'). From there, it is a quick reinsert into the #Errors
from #ErrorsHOLD and we have the desired result set. The
last step is to generate an email and notify our team that there is something
in the error log that may be worth further investigation.
Create the Job
The last thing we needed to do was to put together a job that would run
every ten minutes. We created a job that simply calls the Usp_error
log_Notification procedure (also included in the zip
file). Feel free to modify the job as you see fit, changing the frequency of
the schedule or whatever you desire.
As your environment gets more and more complex, you may find that the number
of alerts increase substantially. Some of the processes that you build while
you have a small shop may not be as effective as more and more servers come
under your control. If you start with a customizable toolbox you can save
yourself some headaches later. With the above solution, you can decide for
yourself what you want to be notified about during an error log scan, and not
have to worry about filtering through the white noise that we all seem to be
swimming in these days.