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

You're Invited: Easily Visualize Your SQL Server Result Sets with Charts and Graphs
 

Simple way to find errors in SQL Server error log


By:   |   Read Comments (4)   |   Related Tips: More > Error Logs

Problem

When managing SQL Server there are so many different places to look for data.  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 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.  It is great to have all of this data, but trying to find your problems can become quite a chore. So how can you find the errors much easier?

Solution

With SQL Server 2005 and later Microsoft has made this a bit easier to set filters, 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 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.

Option 1  - Find SQL Server Errors with sp_readerrorlog

This tip was first written in 2007 and after reviewing the tip to see if it was still relevant, I thought of a much simpler way of doing this using just T-SQL.

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, where 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

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.

Option 2 - Find SQL Server Errors with VBScript

This was the original way of doing this.  This code still works and works with SQL 2000 through SQL 2017, but I would suggest using the T-SQL approach above.

Below is a VBScript that allows you to parse out the error messages. It is not the most elegant piece of code, but it does work.  

The script takes two arguments:

  • logType - 2000 (SQL 2000) or 2005+ (SQL 2005 or later)
  • fileName - name and path of the file that you want to parse

This could be called from a command line such as the following.

ParseLog.vbs "2005+" "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log\ERRORLOG.2"

Before you can use this, you need to save the following code into a new file called ParseLog.vbs. Once you have done this you are ready to roll.  This code will read the file that you specify and will create a secondary file using the same name and appending a ".txt" at the end of the file name, so you can easliy open it with a text editor.

Const FOR_READING = 1 
Const FOR_WRITING = 2 
logType = Wscript.Arguments(0) 
strFileName = Wscript.Arguments(1) 
strNewFileName = Wscript.Arguments(1) & ".txt"

strCheckForString0 = UCase("error:") 

SET objFS = CreateObject("Scripting.FileSystemObject") 

IF NOT objFS.FileExists(strFileName) THEN 
   MsgBox Wscript.Arguments(1) & " is not a legitimate file name."
   Wscript.Quit 1 
END IF 

SELECT CASE logType 
   CASE "2000"
       SET objTS = objFS.OpenTextFile(strFileName, FOR_READING) 
   CASE "2005+"
       SET objTS = objFS.OpenTextFile(strFileName, 1, , true)  
   CASE ELSE 
       Wscript.Quit 1 
END SELECT 

strContents = objTS.ReadAll 
objTS.CLOSE 

arrLines = Split(strContents, vbNewLine) 
SET objTS = objFS.CreateTextFile(strNewFileName, FOR_WRITING) 

errorFound = False 
counter = 1 
errorTime = ""

Dim ErrorArray(20) 

FOR Each strLine IN arrLines 
   IF errorTime <> LEFT(strLine,22) THEN 
       IF errorFound THEN 
           i = 1 
           Do Until i = counter + 1 
               objTS.WriteLine ErrorArray(i) 
               i = i + 1 
           Loop 
           objTS.WriteLine          
       END IF 

       errorTime = LEFT(strLine,22) 
       counter = 1 
       errorFound = False 
       Erase ErrorArray 
       ErrorArray(counter) = strLine 
   ELSE 
       counter = counter + 1 
       ErrorArray(counter) = strLine 
   END IF 

   IF instr(UCase(strLine), strCheckForString0) THEN 
       errorFound = True 
   END IF 
Next
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 2017
  • See if you can make this more robust enter your feedback in the comments section below.

Reader Feedback

Date Comments
8/15/2007 Another way to parse the sql errorlog is using the Microsoft LogParser application.

Here is my sample to find the message "Starting up database 'master'" in all sql 2005 errorlog.

I'm sure you will love the sql syntax to write your query request. A very powerful tool that can parse multiple file (you can see the ERRORLOG.*)

Logparser.exe "select top 10 substr(text,0,22) as Date, substr(text,23,9) as Source, substr(text,32) as Message from \\porphyra\d$\bases\MSSQL$ABO_TEST\LOG\ERRORLOG.* where Message like '%Starting up database \'master\'.%'" -i:textline

Best regards
Jérôme Ostorero


Last Update:

First Published: 2007-08-13


next webcast button


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
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.



    



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

Very usefull artical every SQLadmin. Thanks a lot.


Thursday, March 07, 2013 - 1:00:34 AM - shrikant Khode Back To Top

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
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
 

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.

  • Learn more about SQL Server tools