Simple way to find errors in SQL Server error log


By:   |   Updated: 2021-01-07   |   Comments (9)   |   Related: More > Error Logs


Problem

When managing SQL Server there are so many different places to look for system messages.  These include the error logs, system event logs, profiler data, performance counter data, etc. Once you have collected the data you then need to parse through and interpret the data you collected.  One of these areas where errors and other informational data is stored is the SQL Server error log.

The problem with the SQL Server error log file is that there is so much data collected it is sometimes hard to determine where the real errors lie.  By default all backups and integrity checks are logged in the error log.  In addition, if you are auditing logins these messages are also stored in the error log, so this further compounds the problem.  In this tip we look at a simple script that can quickly parse out errors and related error messages.

Solution

With SQL Server 2005 and later Microsoft has made this a bit easier to set filters when looking at the SQL Server error log, but this is still pretty cumbersome and does not really provide you all of the data you need.  The best approach as with many things is to build your own data parser to find exactly what you need.

Here is a simple view of the Error Log as it normally displays:

error log before

Here is a simple view of the Error Log after the errors have been parsed out:

error log after

As you can see this new version is much easier to read and also only shows you the errors instead of all that additional informational data that is stored in the SQL Server error logs.  In addition, it shows you all of the error lines at the particular time the error occurred for the same source, so you do not need to go back to the error log to get the additional error lines.

Quickly Find and Parse SQL Server Error Log script

This tip was first written in 2007 and after reviewing the tip, it still works with the latest version of SQL Server.

Here is the script.  The only thing you need to change is the error log number you want to pass to sp_readerrorlog (see the comment in the code).  This will produce the output as shown in the above image.

DROP TABLE IF EXISTS #errorLog;  -- this is new syntax in SQL 2016 and later

CREATE TABLE #errorLog (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX));

INSERT INTO #errorLog
EXEC sp_readerrorlog 6 -- specify the log number or use nothing for active error log

SELECT * 
FROM #errorLog a
WHERE EXISTS (SELECT * 
              FROM #errorLog b
              WHERE [Text] like 'Error:%'
                AND a.LogDate = b.LogDate
                AND a.ProcessInfo = b.ProcessInfo)

To figure out the error log number to use, you can look at the error logs in SSMS as shown below.  The current log does not have a number, but each of the archives has a number associated with the archive, so for the above code either use one of these numbers to read that log or use no number to read the current error log.

error log after

Also, if you use an error log number that does not exist, you will get an error message.

If you want to read through all error logs, take a look at this tip Search multiple SQL Server Error Logs at the same time.  You can combine the above with the code in that tip to search for all errors.

Next Steps
  • That's all there is to it.  Hopefully you will find this to be useful.
  • This has been tested with SQL 2000 thru SQL 2019
  • See if you can make this more robust enter your feedback in the comments section below.





get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


Article Last Updated: 2021-01-07

Comments For This Article




Thursday, February 4, 2021 - 9:31:35 AM - Greg Robidoux Back To Top (88165)
Hi Willem,

Those additional checks could be added in as well to the script. I agree there are other things that you may want to check for besides just errors.

Thanks
Greg

Thursday, February 4, 2021 - 5:36:26 AM - Willem Back To Top (88151)
Hi Greg,
I had overlooked that your code will indeed pull out the lines associated with an error; that's nice!

However, I also want to be notified about certain situations which do not report as an 'Error', such as this one:
"SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file xx"

Regards, Willem

Wednesday, February 3, 2021 - 9:39:37 AM - Greg Robidoux Back To Top (88144)
Hi Willem,

Thanks for sharing your approach as well. There are several ways this can be done.

The code in this tip will pull out all of the lines associated with the error based on the timestamp. It looks like when the error is written all of the rows have the same timestamp, so this will pull the row with the error and any associated rows.

Greg

Wednesday, February 3, 2021 - 5:59:40 AM - Willem Back To Top (88141)
Hi Greg,
Thank you for sharing this solution to monitoring SQL Errorlog files!

I am doing something similar, but reversing the approach: not looking for the string 'error', but comparing the entries in the errorlog holding table with a table of items I want to discard (not be warned about).
I maintain a table with entries which I want 'skip' (an 'exclusion table'). After collection, I remove entries with such strings from the holding table.
In that way I end up with a holding table containing only lines that might require follow-up, which may or may not contain the string 'error'. I do this, because there might be interesting information in lines that relate to an error, but do not contain the string 'error', such as this (on 2 lines):
Error: 1105, Severity: 17, State: 2.
Could not allocate space for object 'dbo.SORT temporary run storage: 140754765283328' in database 'tempdb' because .......
If the second line is missing, I still have to go to the errorlog to see the relevant details.

I have set it up in PowerShell, so I can easily run it to monitor any number of machines, with a configurable interval, and use a central holding table.
For the sake of efficiency, I also use a ProgressMonitor table, to only collect errorlog lines from any instance that were created after the last time that instance was checked.

Thursday, January 7, 2021 - 10:59:24 PM - Eitan Blumin Back To Top (88017)
The following script can parse the error log even better, outputting each individual error message in a single row, and also being able to filter the data based on a range of severities:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Monitoring%20Scripts/SQL_Server_Error_Log_Based_on_Severity_with_Full_Message.sql

Saturday, November 16, 2013 - 7:53:49 AM - Ramakrishna Back To Top (27522)

Very usefull artical every SQLadmin. Thanks a lot.


Thursday, March 7, 2013 - 1:00:34 AM - shrikant Khode Back To Top (22620)

Hi,

  How can I import chinese character in sql server through SSIS or import /  export wizard in sql server MS.

 

Thanks,

Shrikant

 


Wednesday, October 13, 2010 - 4:05:56 PM - admin Back To Top (10262)
Thanks.  Here is a another tip that discusses the use of xp_readerrorlog

http://www.mssqltips.com/tip.asp?tip=1476


Wednesday, October 13, 2010 - 3:14:14 PM - Chris Malone Back To Top (10261)
 

And an even easier way than fighting with LogParser syntax is to use the undocumented proc xp_readerrorlog, like this example:

xp_readerrorlog 0, 2, 'could not obtain information about Windows NT group/user', null

...where parameters to the proc are as follows:

  • Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log. The extended stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error logs or SQL server Agent logs exist and when they were created. NOTE: extended stored procedure xp_enumerrorlogs parameter (1) works just like xp_ReadErrorLog parameter (2). A value of 1 shows available SQL error logs and a value of 2 shows Agent logs. The default value is 0.
  • Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.
  • Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.
  •  Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.


  • download














    get free sql tips
    agree to terms