Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SSMS Tip: Merging ERRORLOGs from different machines using Log File Viewer

Posted by blakhani on March 6, 2014


For those who don’t know what/where is SQL Server ERRORLOG, please read this

Log File Viewer is an interesting feature of Management Studio and I have been ignoring it (you too??). This tool as saved a lot of time for me so thought of sharing some cool features of this tool. This can load various logs including system event log, application event log, DB Mail Log and SQL Agent log.

As a part of my regular work, I need to look at SQL Server ERRORLOG to look for various information about environment like SQL version and Edition, Processors and memory etc. If you want to know where ERRORLOG is located, then read here.

While working on a case on AlwaysOn Availability Group, customer provided us errorlog from two machines. To complete my analysis, I have to merge the file from both servers so that I can look at series of event happening across the server. Since customer was not having access to physical machine, he used management studio to “export” the errorlog.

Here are the steps to export the log using “Log File Viewer”

image

Once the viewer is opened, it opens current ERRORLOG file from the server. If we want to see more we can select checkbox on the left hand tree as highlighted below.

image

Export button can be used to Save the content on local machine. This is useful when we don’t have access to physical server to get file.

To demonstrate “merge”, I have exported current ERRORLOG from three servers and saved them as ERRORLOG_SQLPAPA, ERRORLOG_SRV1 and ERRORLOG_SRV2. I have kept all files in same machine and launched SSMS there. First, we need  to load the log as shown below.

image

One all are loaded, we can see them on left hand tree. Select the checkbox if it has to be loaded on right side grid.

image

I have selected all three files. Now, in the grid there is one interesting column, Log Source which shows the file which is loaded. I have dragged the column header and moved to visible location.

image

If we sort by date column, we can easily see the series of message in errorlog based of time. Since we have “Log Source” column, it possible to find server name as well. That’s why I have given File Name which is meaningful. In above screenshot I can see that top 3 messages are from different servers and I know the time as well.

You can be more creative and use “Filter” to load only specific data which you are interested.

Hope you liked this cool feature. Please provide feedback via comments.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    One Response to “SSMS Tip: Merging ERRORLOGs from different machines using Log File Viewer”

    1. Anil said

      Awesome article…Keep sharing such great work

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: