Searching the SQL Server ErrorLog with a Stored Procedure

Welcome to the new Coding in SQL Newsletter.  In the previous two volumes, this newsletter was titled “T-SQL UDF of the Week”.  The name changes reflects the change in focus from strictly UDFs to all types of coding for SQL Server.   Not that UDFs won’t be prominent in the newsletter, it’s just that they won’t have the near exclusive place that they had in the first two volumes.  By the way, all issues of the first two volumes are on line and you can find the archives at: http://www.novicksoftware.com/udfofweek/udfofweekarchive.htm

Along with a new title for the newsletter, a new web site design is coming.  This site will be able to support more articles, more newsletters, more downloads, and more other types of content.  You don’t need to worry about this at all.  You’re subscription remains in the same place for now and when the change arrives the link to manage your subscriptions will always be at the bottom or top of each newsletter issue.

As I thought about the new newsletter, I decided on a few changes.  The first change is a wider role for all types of SQL coding beyond the User-Defined Function.  I feel that this is necessary because of the upcoming changes that SQL Server 2005 will bring, particularly in the ability to incorporate .Net CLR based code written in C# or VB.Net.  This is going to change a lot about how we code SQL Server based applications and it’s too important to ignore.  In addition, I think it’s pretty cool.  I like coding in C# and VB.Net and when then task is right, it’s much quicker than writing in T-SQL.

The second change in the newsletter is going to be themes.  The first two newsletter volumes were mostly stand alone issues.  That is there wasn’t much of a connection from one issue to the next.  That wasn’t always the case.  There was the occasional series of two or three issues that were connected by a common problem.  In the new newsletter I’m going to adopt themes that run for 3 to 5 issues all of which discuss a common topic.

One of the things that won’t change about the newsletter is code.  There will almost always be some sort of code in each newsletter.  But enough about the newsletter, let’s get on with it….

The theme for January 2005 is logging of messages.  I’m not talking about the transaction log that SQL Server maintains for transaction and database recovery, I’m talking about the process of getting a message from a piece of code to the person responsible for responding to the message.  The first such person is usually the DBA for the site.  But if the message reflects a problem in the code, it may often have to be passed on to a developer who can modify the application.

There are several ways to log messages in SQL Server; I’d guess that you’re familiar with many of them.  Here are some that I know about:

• The SQL Server ErrorLog
• The SQL Server Agent message log
• The Windows Event Log
• An application created log/trace facility

The variety of names given to the SQL Server ErrorLog is unfortunate.  In SQL Enterprise Manager they’re called the “SQL Server Logs” and you’ll find them in the Management node in each instance’s tree.  In parts of the Books-On-Line they’re called the SQL Server ErrorLog, which is the name that I try and use.  Other places often refer to them as the SQL Log. 

There are seven log files: the current log and six archival logs.  The most interesting log is generally the current log.  And this issue’s code can be used to convert the log to a rowset so that it can be processed more easily.  The procedure is dba_ErrorLog_Search, which turns the log messages into a result set and provides some searching capability.   Here’s the CREATE PROC script:

CREATE PROCEDURE dbo.dba_ErrorLog_Search

   @StdFilter BIT = 0 -- 1=Apply the Standard Filter
 , @Filter nvarchar(4000) = NULL -- Additional search filter
  -- used with LIKE, will be surrounded by %.  Not used when NULL
/*
* Returns a table of ErrorLog messages from the current log
* in order that they were created.  When @StdFilter is not
* equal to 1, many common messages are filtered out of the
* result. @Filter is an additional filter than can be used to
* target a specific string.
*
* Example:
exec dba_ErrorLog_Search 1, default
exec dba_ErrorLog_Search default, 'copyright'
*
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as Coding in SQL Newsletter Vol 3 #1 1/4/05   
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS
SET NOCOUNT ON

CREATE TABLE #ErrorLog (Message varchar(255)
                      , ContinuationRow tinyint
                      , ID int identity(1,1) primary key)

-- Populate the temporary table with all log messages.
INSERT #ErrorLog (Message, ContinuationRow)
        EXEC master..xp_readerrorlog

-- Surround the filter wtih %
IF @Filter IS NOT NULL SET @Filter = '%' + @Filter + '%'

SELECT Message
            FROM #ErrorLog
            WHERE (0=@StdFilter
            OR -- The standard filters apply
             (     Message NOT LIKE '%.TRN%'
               AND Message NOT LIKE '%Log backed up%'
                           AND Message NOT LIKE '%Run the RECONFIGURE%'
               AND Message NOT LIKE '%Database backed up%'
               AND Message NOT LIKE '%.BAK%'
               AND Message NOT LIKE '%Copyright (c)%'
               AND Message NOT LIKE '%All rights reserved%'
              )
           )
        AND (@Filter IS NULL
              OR Message LIKE @Filter
            )
            ORDER BY ID

DROP TABLE #ErrorLog
GO

There's no GRANT statement with this script because I don't think it's a good idea to allow anyone other than DBAs to use it.  However, you may think differently and you might want to grant permissions to use dba_ErrorLog_Search to a wider group of users.

There are two ways to use this procedure to apply filters on the error log messages.  The first way is a set of standard filters that eliminate many of the most common messages leaving only the more interesting ones.  The standard filtering is applied by setting @StdFilter to 1.   The second way to filter is to provide a search string.  For example using ‘copyright’ would search for all messages with the word copyright in them.

The two methods can be combined searching only messages that satisfy the standard filters and in addition looking for a particularly for a word or phrase.  The word or phrase is usually a database name, table name, or part of a frequently occurring message.

Here are some examples with their results:

exec dba_ErrorLog_Search 1, default

(Results - Truncated on the right)

			Message                                                           
----------------------------------------------------------------- 
2005-01-03 08:00:54.95 server    Microsoft SQL Server  2000 - 8.0
	Dec 17 2002 14:22:05 
	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2005-01-03 08:00:54.95 server    Server Process ID is 1416.
2005-01-03 08:00:54.95 server    Logging SQL Server messages in f
2005-01-03 08:00:55.11 server    SQL Server is starting at priori
2005-01-03 08:00:56.32 server    SQL Server configured for thread
2005-01-03 08:00:56.39 server    Using dynamic lock allocation. [
2005-01-03 08:00:56.54 server    Attempting to initialize Distrib
2005-01-03 08:00:58.20 spid3     Starting up database 'master'.
2005-01-03 08:01:00.92 spid3     0 transactions rolled back in da
2005-01-03 08:01:01.01 spid3     Recovery is checkpointing databa
2005-01-03 08:01:01.42 server    Using 'SSNETLIB.DLL' version '8.
2005-01-03 08:01:01.67 server    SQL server listening on 192.168.
2005-01-03 08:01:01.67 spid5     Starting up database 'model'.
2005-01-03 08:01:01.67 server    SQL server listening on 127.0.0.
2005-01-03 08:01:01.68 server    SuperSocket Info: Bind failed on
2005-01-03 08:01:01.70 server    SuperSocket Info: Bind failed on
2005-01-03 08:01:01.71 server    SuperSocket Info: Bind failed on
2005-01-03 08:01:01.79 spid3     Server name is 'NSL3'.
2005-01-03 08:01:01.79 spid8     Starting up database 'msdb'.
2005-01-03 08:01:01.79 spid9     Starting up database 'pubs'.
2005-01-03 08:01:01.79 spid10    Starting up database 'Northwind'
2005-01-03 08:01:01.79 spid11    Starting up database 'UDFofWeek'
2005-01-03 08:01:03.78 spid9     Starting up database 'TSQLWorkin
2005-01-03 08:01:04.29 spid5     Clearing tempdb database.
2005-01-03 08:01:04.40 spid10    Starting up database 'QuoteWODev
2005-01-03 08:01:05.89 spid8     Starting up database 'Replcation
2005-01-03 08:01:07.23 spid10    Starting up database 'distributi
2005-01-03 08:01:08.65 spid8     Starting up database 'Scratch'.
2005-01-03 08:01:09.62 spid11    Starting up database 'SEA'.
2005-01-03 08:01:10.15 spid10    Starting up database 'SQLDBADEV'
2005-01-03 08:01:11.95 spid10    Starting up database 'SQLDBADEV2
2005-01-03 08:01:12.98 spid5     Starting up database 'tempdb'.
2005-01-03 08:01:14.51 spid9     Starting up database 'SVASProExt
2005-01-03 08:01:17.68 spid10    Starting up database 'SEAProExtr
2005-01-03 08:01:18.96 server    SQL server listening on TCP, Sha
2005-01-03 08:01:18.96 server    SQL Server is ready for client c
2005-01-03 08:01:19.48 spid10    Starting up database 'SVASProExt
2005-01-03 08:01:20.84 spid11    Starting up database 'CRDIRECT'.
2005-01-03 08:01:20.89 spid9     Starting up database 'freedbDev'
2005-01-03 08:01:23.01 spid9     Starting up database 'ROES1'.
2005-01-03 08:01:23.25 spid11    Starting up database 'AppSessTes
2005-01-03 08:01:24.96 spid9     Starting up database 'AppSessTes
2005-01-03 08:01:25.09 spid8     Starting up database 'DYNAMICS'.
2005-01-03 08:01:25.21 spid11    Starting up database 'TWO'.
2005-01-03 08:01:25.76 spid8     Analysis of database 'DYNAMICS' 
2005-01-03 08:01:26.49 spid9     Starting up database 'SBM01'.
2005-01-03 08:01:26.59 spid11    Starting up database 'SvasProExt
2005-01-03 08:01:27.20 spid9     Analysis of database 'SBM01' (26
2005-01-03 08:01:29.42 spid3     Recovery complete.
2005-01-03 08:01:29.42 spid3     SQL global counter collection ta
2005-01-03 08:01:29.81 spid3     Launched startup procedure 'sp_M
2005-01-03 08:01:29.93 spid3     Launched startup procedure 'smDE
2005-01-03 08:01:37.31 spid51    Using 'xpsqlbot.dll' version '20
2005-01-03 08:01:39.40 spid52    Using 'xplog70.dll' version '200
2005-01-03 08:01:39.40 spid52    Error: 50001, Severity: 10, Stat
2005-01-03 08:01:39.40 spid52    Pinned table SQLDBADEV2.dbo.dba_
2005-01-03 08:01:39.42 spid52    Error: 50002, Severity: 10, Stat
2005-01-03 08:01:39.42 spid52    Pinned 1 tables in database SQLD
2005-01-03 08:01:40.40 spid52    Using 'xpstar.dll' version '2000