How to parse SQL Server Error Log with Log Parser 2.2

In this short blog post I want to explain why Log Parser 2.2 is not the best tool to parse SQL Server Error Log.

OK, But why to use tools to parse Error Log?

In the life of every DBA comes such a moment when he or she is speechless. I think this is one of them:

That’s right! The Error Log file on above screenshot is 56 GB big. You can forget about opening it in SQL Server Management Studio – it’s truly impossible.

Such big file is not an issue when everything works fine and you don’t have any reason to look into it. The real problem starts when you need to look for error entries or just verify what happened at given time.

That’s the reason why I started to look for tools which may be helpful in such a task. And that’s how I discovered Log Parser 2.2.

What Log Parser 2.2 is?

On TechNet site we can find encouraging description:

Log Parser 2.2 is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart. Most software is designed to accomplish a limited number of specific tasks. Log Parser is different… the number of ways it can be used is limited only by the needs and imagination of the user. The world is your database with Log Parser.

In addition it’s a command-line tool and you can use SQL queries to filter data you are interested in, so it looks promising, isn’t it? Tool can be downloaded from Microsoft Download Center.

How to parse Error Log with Log Parser 2.2?

After installation we can immediately begin to use our new tool. On first step we need to choose the input type from the many available options. Unfortunately there is no explicit support for SQL Server log files. As a result of many attempts, it turned out that -i:TEXTLINE is the only somehow working option.

C:\Program Files (x86)\Log Parser 2.2>logparser.exe -i:TEXTLINE "SELECT * FROM c:\Temp\ErrorLog\ErrorLog"
LogFilename               Index Text
------------------------- ----- ----------------------------------------------------------------------------------------------------------------
c:\Temp\ErrorLog\ERRORLOG 1     2017-06-07 07:56:43.26 Server      Microsoft SQL Server 2014 - 12.0.2370.0 (X64)
c:\Temp\ErrorLog\ERRORLOG 2             Jun 21 2014 15:21:00
c:\Temp\ErrorLog\ERRORLOG 3             Copyright (c) Microsoft Corporation
c:\Temp\ErrorLog\ERRORLOG 4             Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
c:\Temp\ErrorLog\ERRORLOG 5     -
c:\Temp\ErrorLog\ERRORLOG 6     2017-06-07 07:56:43.28 Server      UTC adjustment: 2:00
c:\Temp\ErrorLog\ERRORLOG 7     2017-06-07 07:56:43.28 Server      (c) Microsoft Corporation.
c:\Temp\ErrorLog\ERRORLOG 8     2017-06-07 07:56:43.28 Server      All rights reserved.
c:\Temp\ErrorLog\ERRORLOG 9     2017-06-07 07:56:43.28 Server      Server process ID is 1272.
c:\Temp\ErrorLog\ERRORLOG 10    2017-06-07 07:56:43.28 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
Press a key...

Unluckily, with this option we get only very simple result-set with three columns. First column [LogFileName] provides source filename, second [Index] which is a row number, and third [Text] contains whole single line from log file.

Afterwards with a little help of SUBSTRINGfunction we can get the following result:

C:\Program Files (x86)\Log Parser 2.2>logparser.exe -i:TEXTLINE "SELECT SUBSTR(Text,1,22) AS [Timestamp], SUBSTR(Text,23,11) AS Source, SUBSTR(Text,35) AS [Message] FROM c:\Temp\ErrorLog\ErrorLog"
Timestamp              Source      Message
---------------------- ----------- -----------------------------------------------------------------------------
017-06-07 07:56:43.26  Server      Microsoft SQL Server 2014 - 12.0.2370.0 (X64)Jun 21 2014 15:21:00
Copyright (c) Microsof t Corporati n
Enterprise Edition: Co re-based Li ensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
-                      -           -
017-06-07 07:56:43.28  Server      UTC adjustment: 2:00
017-06-07 07:56:43.28  Server      (c) Microsoft Corporation.
017-06-07 07:56:43.28  Server      All rights reserved.
017-06-07 07:56:43.28  Server      Server process ID is 1272.
017-06-07 07:56:43.28  Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
Press a key...

It’s not really what I expected, but finally it allows to do very basic filtering:

C:\Program Files (x86)\Log Parser 2.2>logparser.exe -i:TEXTLINE "SELECT Index, Timestamp, Source, Message USING Index AS Index, SUBSTR(Text,1,22) AS [Timestamp], SUBSTR(Text,23,11) AS Source, SUBSTR(Text,35) AS [Message] FROM c:\Temp\ErrorLog\ErrorLog WHERE Message like 'Error%'"
Index Timestamp              Source      Message
----- ---------------------- ----------- --------------------------------------
60    017-06-07 07:56:50.68  Logon       Error: 18456, Severity: 14, State: 38.
62    017-06-07 07:56:53.21  Logon       Error: 18456, Severity: 14, State: 38.
492   017-06-07 09:01:54.31  Logon       Error: 18456, Severity: 14, State: 38.
494   017-06-07 09:02:04.32  Logon       Error: 18456, Severity: 14, State: 38.
1835  017-06-11 00:47:08.87  Logon       Error: 18456, Severity: 14, State: 38.
1877  017-06-11 00:49:50.29  Logon       Error: 18456, Severity: 14, State: 38.
1879  017-06-11 00:50:01.27  Logon       Error: 18456, Severity: 14, State: 38.
3358  017-06-14 13:20:27.75  Logon       Error: 18456, Severity: 14, State: 38.
3360  017-06-14 13:20:52.78  Logon       Error: 18456, Severity: 14, State: 38.
3362  017-06-14 13:30:42.30  Logon       Error: 18456, Severity: 14, State: 38.
Press a key...

And that’s it…You can try, but you will not get more from it. That’s not impressive and I now know it. As I said at the beginning it’s not really best tool for SQL Server Error Log parsing.

Summary

Although Log Parser 2.2 may be a great tool for parsing Event Log, Registry, Active Directory, IIS Logs, and many others system related logs, it doesn’t have good support for SQL Server Log files, hence is not very helpful. In case you have to parse huge SQL Server Error Log file I encourage you to use different tools. Probably PowerShell may be your best choice.

-Marek

Share it: