What is Microsoft LogParser?
I've been using Microsoft LogParser for a while. It is a great little (yet powerful) tool to query text-based data logs. It is very flexible. It can be use to query comma/tab separated files, IIS logs, Windows Event logs, etc. In term of outputs, we can output the result to datagrid, graph, database, text files, etc. One thing that I love about Microsoft LogParser is that we use SQL expression to query the logs (I am a bit biased on this since I am a DBA).
Microsoft LogParser is a command line utility. The installation file can be located here. There are some people that developed GUI for Microsoft LogParser. One of them can be found here.
Using Microsoft LogParser to Read SQL Error Log
SQL Error Logs in SQL Server provides a lot of information that can be use by DBA to troubleshoot issues with SQL Server. It contains server error messages, backup and restore messages, process messages, etc. We can easily view SQL Error Log using SQL Log Viewer that comes with Microsoft SQL Server Management Studio.
SQL Error Logs by default is stored in the following folder: C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG\. It has file name of either ERRORLOG or ERRORLOG.x. They are text-based logs, and we can use Microsoft LogParser to query the logs.
To output all of SQL Error Logs into a datagrid, you can use the following command with Microsoft LogParser:
LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID
A bit of explanation of the option used:
| -i:TSV | This is to indicate that the input file used is a TSV (Tab Separated Values) file. |
| -iSeparator:spaces | This is to specify that we use spaces as the separator between values. |
| -iHeaderFile:C:\SQLErrorLogHeader.txt | The SQLErrorLogHeader.txt file can be downloaded from here. If you open one of the SQL error log, you would notice that it does not have any column header. What this option does is that it tell LogParser to use SQLErrorLogHeader.txt as the header format file. We can use the header specify in the header format file in our SQL query. |
| -i:headerRow:OFF | This just to tell LogParser that the first row of the input file is not header. |
| -nskiplines:5 | With this option, we skipping the first 5 lines from the input files. If you look at one of the SQL Error log file, the first 5 lines of the error log are just the SQL Server version and build information. |
| -o:DATAGRID | This is to indicate that we want the output into datagrid format. |
Now let's look at the SQL query that we use:
SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'
Within the select clause, we use the column names specify in the SQLErrorLogHeader.txt. One quick thing to point out here is that, the TO_TIMESTAMP function is being used to convert text into datetime. Within the from clause, we just put the location of the SQL error log files. The neat thing about this is that it accepts wildcard, so LogParser can process multiple files at once.
For the SQL query, you can be as creative as you like. Some examples:
Let say that you want to find out messages in SQL Error Logs that are older than 1st January 2010, you can use the following command:
LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < TO_TIMESTAMP('01/01/2010', 'MM/dd/yyyy')" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID
Or let say that you are trying to find out about database backups that was done:
LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < Message LIKE '%backup%'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID
Great idea but I could not find the header file - the link isn't working - could you post the file/new link?
ReplyDeleteFor the SQLErrorLogHeader.txt header file, you can create a new text file and then put the following into a text file content:
DeleteDate Time Source Message
Let me know if it does not work.
Having problems getting the TO_TIMESTAMP part to work for the DateTime. I keep on getting the error:
ReplyDeleteError: SELECT clause: Syntax Error: unknown field 'DATE'.
The closest match for input format 'TSV' is 'DateTime'.
Thank you for posting this idea.
Thank you for the feedback. I think the error that you are getting is because SQL Error Logs don't have column header. So we need to create a text file that map the column header. For SQL Error log, create a text file with any name you like (in my case, I name it "SQLErrorLogHeader.txt"). In the text file, I just have:
DeleteDate Time Source Message
Then when you run the Log Parser, use the following parameters:
-iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF
So is there supposed to be a space between Date and Time in the header file? I see you are not selecting two fields in the actual SQL query. I think that might be part of my confusion.
DeleteThere is a space between Date and Time in the header file. They are being treated as 2 different columns. In the SQL query, I actually concatenate the Date + space + Time using the STRCAT function and then convert the concatenated string to date time format yyyy-MM-dd hh:mm:ss:
DeleteSELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime
By the way, there is a gotcha when using the TO_TIMESTAMP. If it could not convert string, they would appear as NULL. Alternatively you can also not convert the string:
SELECT STRCAT(STRCAT(DATE,' '), TIME) as DateTime