How do I read log file || SQL Logs || Text Based Log

Reading log file is a primary task for troubleshooting and finding root cause. However, it is also important to know how to read log file quickly without putting lot of efforts.

Most of the busy application have big size of log file or multiple log files in a day.

Hence, finding specific keyword or open big size of file could be time consuming and annoying task. however, if we know the proper technique to read the same then reading log file would be the fun.  

In this blog I would like to share how I read SQL error logs and any text-based log file.

 

Read SQL Error logs:

SQL Error log can be read using SSMS and T-SQL.

To read SQL error log using SSMS:

 Open SSMS-->SQL Server Agent-->Error Logs-->Open Log

 

We can easily filter and search require keyword using GUI. also export the log in CSV and TXT format.

The same way we can check logs for SQL Server Agent and Database Mail. also, you can load external log files as well. click on option Load Log and select the log file which need to be read. 

I prefer to read error log using T-SQL.

There is undocumented stored proc and extended stored proc to read error log.

Those are 1) sp_readerrorlog 2) xp_readerrorlog

sp_readerrorlog has 4 parameters as below.

1. Log file number (Data type: int)
2.  Log file type (i.e. null or 1 for SQL server error log and 2 for SQL Agent log ) (Data type: int)
3. Keyword 1 / String 1 (Data type: nvarchar)
4. Keyword 2 / String 2 (Data type: nvarchar)

Ex:

Sp_readerrorlog 0,1,'devking',''

The same way we can use xp_readerrorlog which has 7 parameters.

1. Log file number Ex: 0,1, 2... etc.
2. Log file type (i.e. null or 1 for SQL server error log and 2 for SQL Agent log)
3. Keyword 1 / String 1
4. Keyword 2 / String 2
5. Start Date
6. End date
7. Sort result by Ex: asc or desc

Ex:

xp_readerrorlog 0,1,N'login',N'',N'2020-12-02',N'2020-12-03','desc'

 

This is all about SQL server logs. Lets see another way.

Application log generally comes with extension * .log or *.txt. if log size is huge then there is always a trouble to open and go through it. Most of the time it gets hanged.

To avoid the same, we can use log parser tool. this tool can be downloaded from Microsoft official sites.

Download Log Parser 2.2 from Official Microsoft Download Center

Let’s see how we can use Log parser to read data.

After log parser installation, open the log parser.

Log Parser is open in CMD look alike window.

In order to read log file use command as below.

LogParser.exe "select * from E:\SQL_Log\SQL_Server.log" -o:datagrid

In this command we are using parameter -o:datagrid. Means output will open in data grid format as given below.

 

 

 

We can load 10 rows or all rows at once irrespective of log file size. I have tried with around 1GB size of log file. It loaded smoothly.  

Log parser supports multiple log formats and functions. I found link https://logparserplus.com/functions/ useful to understand the same.

Let me know how do you read log file. 🙂