By: Greg Robidoux | Comments (9) | Related: > 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:
Here is a simple view of the Error Log after the errors have been parsed out:
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.
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.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
