Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Script to Retrieve All Error Numbers and Messages from the SQL Server Error Log


By:   |   Read Comments (2)   |   Related Tips: More > Monitoring

Problem

The undocumented function xp_readerrorlog provides us the ability to read the SQL Server error log using T-SQL and perform some basic text searches, but this function is very limited.  For example, if we want to retrieve the error number and related message, since they are located on different rows, and a text search won’t work on those cases.

First, let us see how any given error is stored in the error log, just doing a simple check using SQL Server Management Studio of any error, you can see two rows, first the error number and then the error message:

sample error message displayed in the error log using SSMS

For this example, if we would like to obtain all the errors in the current error log, we would use something like this, by filtering the text:

--- Filter all events with the word 'Error' in the text
EXEC master.dbo.xp_readerrorlog 0, 1,"Error", null, null, null

and we obtain the following output:

simple xp_readerrorlog output containing all the records with the "error" word in it.

As you can see, we only have the error numbers and that is not very descriptive by itself.

So now imagine that you want also the related error message, that is stored in the next row after the error number, you must write some custom code to achieve this.  Check out this tip to see how this can be done.

Solution

Starting with SQL Server 2012, the LAG() function was introduced and it allows any query to access a row given an offset from the current row.  Using the LAG() function and a temporary table, I will show you how an error number and its related message can be retrieved.

How does the SQL Server LAG() Function work?

Basic syntax of the SQL Server LAG function is:

LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

To be able to use this function, we first store all the error log output in a temporary table, after that we apply the function with the table order by date, to retrieve the very next row, that contains the error message.

This is just a part of the script, just to see where this function is used:

SELECT   
   [date],
   [processinfo], 
   [text] as [MessageText],
   LAG([text],1,'') OVER (ORDER BY [date]) as [error]
FROM #ErrorLog

In this case, the OVER is an order by date, and we use an offset of 1, to obtain the next row.

The empty string '' indicates to the function as a default value if no next row exists.

Note that we use a temporary table #ErrorLog to be able to achieve this, since we cannot perform it directly from the xp_readerrorlog stored procedure output.

Using the full script

As we stated earlier, that SELECT statement was just a part of the script, at a glance, the full script will do the following:

  • Declare variables to use and assign them values, so we can parametrize it.
  • Declare the temporary table to use.
  • Inserting all the error log entries in the temporary table from the error log.
  • Select from the temporary table the required values.
  • Filter the results with the text we want. For this example, we will filter all the entries with the text “error”.
  • Drop the temporary table.

This is the full script to obtain the error numbers and messages:

DECLARE @ArchiveID INT
   ,@Filter1Text NVARCHAR(4000)
   ,@Filter2Text NVARCHAR(4000)
   ,@FirstEntry SMALLDATETIME
   ,@LastEntry SMALLDATETIME

SELECT @ArchiveID = 0
   ,@Filter1Text = ''
   ,@Filter2Text = ''
   -- this will only take the logs from the current day,
   --you can change the date ranges to suit your needs
   ,@FirstEntry = DATEADD(DAY, - 1, getdate())
   ,@LastEntry = getdate()

CREATE TABLE #ErrorLog (
   [date] [datetime] NULL
   ,[processinfo] [varchar](2000) NOT NULL
   ,[text] [varchar](2000) NULL
   ) ON [PRIMARY]

INSERT INTO #ErrorLog
EXEC master.dbo.xp_readerrorlog @ArchiveID
   ,1
   ,@Filter1Text
   ,@Filter2Text
   ,@FirstEntry
   ,@LastEntry
   ,N'asc'

SELECT *
FROM (
   SELECT [date]
      ,[processinfo]
      ,[text] AS [MessageText]
      ,LAG([text], 1, '') OVER (
         ORDER BY [date]
         ) AS [error]
   FROM #ErrorLog
   ) AS ErrTable
WHERE [error] LIKE 'Error%' 
-- you can change the text to filter above.

DROP TABLE #ErrorLog

Just run the above code and you will obtain output like this:

see the error number and error message in the same row

Now, if you filter the text by “error” you can obtain more information about the error message related to the error.

Please note that at the beginning of the script there are some parameters that are used, so for example if you have an automated process to retrieve this information, you can filter it by date, since by default, we retrieve information just for the current day.

Also, you can replace the temporary table information and use a permanent table, so you can store this information for later analysis.

For the text to filter, additional messages you want to track can be included as well or only to filter specific error numbers.

Next Steps

  • Remember, the LAG() function is available since SQL Server 2012, so this script won’t work prior to that version.
  • You can check the LAG official documentation at the Microsoft site.
  • You can customize the query to make it run automatically and store the results to any table. Also, you can put the date range to suit your needs.
  • Note that some special error log events will require custom filtering, but you can implement this custom filtering using the script provided as a base.
  • In the SQL Server feature request site, I have submitted a request to include a documented, more robust DMO to be able to query the error log via T-SQL, you can support the feature here.
  • Take a look at this tip as well Simple way to find errors in SQL Server error log.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, August 10, 2018 - 9:12:13 PM - Eduardo Pivaral Back To Top

Hi Salam,

the issue you have could be due that the asterisk (*) you have in your search string, you could try 'Attempt%'  .

Also please note that the text you are searching already has the complete message on it. My tip is useful for multi-line log entries.

(you can simulate one by just trying a sql server login with a wrong password)

Hope this helps!


Friday, August 10, 2018 - 10:18:50 AM - Salam Back To Top

 Hi, interesting post. I tried your script but it returns nothing as I have no errors. I commented tha last statement "WHERE [error] LIKE 'Error%' " I get some information, in one of the lines I have the following in the 

Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.

I replaceed 'Error%' by 'Attempt*%'  

but it returns nothing

 Thanks for your help


Learn more about SQL Server tools