Search SQL Server error log files

Each instance of SQL Server logs information about its processing to a file known as the error log. Depending on how long an instance has been up and what is being logged, the log files might be small or large. When the log files are small, they are fairly easy to browse using SQL Server Management Studio (SSMS). But when they are large, it is cumbersome to browse through them to find individual error log messages using SSMS. There are even times when the error log file is so large it can’t even be opened up using SSMS. This article will show you a few different ways to browse and search SQL Server error log files.

Using SSMS to search and filter large SQL Server error log files

When browsing a large error log file with SSMS, it can take a long time just to scroll through the file to find the portion of the log that you might be interested in reviewing. I find it easier to use the search and filter options to find the information in large error log files. I’ll demonstrate how to use these options to find information in large error log files.

Using the search option

The search option is useful for finding the next occurrence of a string of characters in the log. To search, you can just browse through one of the archived log files, as shown in Figure 1.

Figure 1: Browsing my error log file

Figure 1 shows the beginning of the error log file, and the entries are sorted by the date/time from the oldest to the newest. You can see the Search function outlined by a red box at the top of the screenshot. To use the search function, just click on this search icon, which brings up the search dialog shown in Figure 2.

Figure 2: Search selection dialog

To search, just enter the string of characters you want to find in the Search for: field. The characters can be case-insensitive or case-sensitive based on whether the Match case check box is checked. You can also search just the Message column or all the columns depending on if the Search Message column only box is checked. When an error log file spans many days, you could uncheck this checkbox to search for a particular date/time string in the log. By doing this, the error log can be reposition to display a specific day in the log in a log file that contains multiple days.

For this demonstration, enter the string error in the Search for: criteria. Once the search criteria are filled in, the Search button is enabled, as shown in Figure 3.

Figure 3: Enabling Search Button

When clicking the Search button, the error log position is relocated to the first occurrence of the string error, as shown in Figure 4.

Figure 4: Repositioned to first occurrence of the string

Click the Search button again to move to the next message text that contains the string error, as shown in Figure 5.

Figure 5: Next occurrence of the string “error”

By reviewing Figure 5, you can see the search function found the string error just a few lines down further in the log (the actual string error is located out of view to the right). By clicking the search button repeatedly, you can progressively work through the large error log file finding all the messages that contain the string error. Once the last message is found, the search will start over from the top if you click the button again.

Using the search button repeatedly could be a little tedious, especially if the log file contains many messages with string error. Another way to find all the messages without clicking and scrolling is to use the filter option.

Using the Filter Option

The filter option makes it a little easier to find all the occurrences of a string in the error log file. It does this by sifting through a large error log file and only displaying those rows that meet the filter criteria. Filtering is handy when you want to view specific log entries in a very large log file. To bring up the filter criteria, you need to click on the Filter options in the Log File Viewer window, as shown in Figure 6.

Figure 6: Selecting the Filter Option

When the filter option is clicked, the dialog box in Figure 7 is shown.

Figure 7: Filter Options

As you can see from Figure 7, there are several different filter selection options from which to choose. You can use one, or more of these filter options to identify those error log records you want to display. Table 1 lists the descriptions for each of these different filter options.

Table 1: Descriptions for each filter option

Filter Name	Description
User	The user name that is associated with the log entry
Computer	The computer that is associated with the log entry
Start Date	Log entry must be created on or after this date
End Date	Log entry must be created on or before this date
Message contains text	Log entry message must contain this text (case-insensitive)
Source	The source of the log entry
Instance Name	The instance Name that is associated with the log entry
Event	The event id that is associated with the windows log entry

To demonstrate how to use the filter dialog to find specific error logs, first try to find the ERRORLOG file directory name using the Message contains text filter item. The error log directory name is displayed on an error log line item that contains the string Logging SQL Server messages in the message text. Therefore, all you need to do is enter this string in the Message contains text filter item, check the Apply filter checkbox, and then click on the OK button, as shown in Figure 8.

Figure 8: Applying Filter

After clicking the OK button, only the error log lines that contain the text are displayed, as shown in Figure 9. If the Apply Filter checkbox is not checked, before clicking on the OK button, the filter won’t be applied.

Figure 9: Results of message text filter

Using the filter item is especially useful for finding those messages that are hidden amongst all the messages you are not interested in. I also find using the Start Date and End Date filters extremely useful to find log entries for a specific date range. The date range filter is handy when the error log file is very large and contains multiple days of error log records.

Out of memory errors when viewing large logs

If SQL Server has been up for a while and the error log has not been cycled, or a lot of messages have been written to the log file over a short time, then the error log might be very large — possibly in the gigabyte size range. If you try to open one of these gigabyte log files using SSMS, a memory exception will occur. Figure 10 shows the out of memory exception that can occur when opening one of the large error log files.

Figure 10: Out of memory exception when trying to view a large error log file

I got this error when I tried to open one of my large, archived log files that was over 8 GB in size. When this error occurred, some of my log records were loaded into the viewer. I could still use the search option, but I got another memory exception when I tried to use the filter option.

If you are trying to use the SSMS to view large log files and having memory issues, this doesn’t mean you are out of luck. There are other options to view, search and filter these large log files.

Using a text editor to view a large log file

One option to view a large log file is to use a text editor. But it can’t just be any text editor; it needs to be a text editor that can read a large file. I have downloaded and used UltraEdit in the past to open large error log files. I’m not endorsing UltraEdit; I only mention it here because it is one of the editors I have used in the past to look at large log files. Keep in mind that UltraEdit is not free software; you need to have a license to use this product long-term. Before you consider downloading any text editor off the internet, make sure you understand the software’s uses and license requirements being downloaded.

Programmatically searching the error log file

Another option for searching those larger log files is to do it programmatically. SQL Server provides an undocumented extended stored procedure named xp_readerrorlog that can be used to search the error log and the SQL Agent log files.

Listing 1 is an example of how I used this undocumented stored procedure to search the active error log file on one of my instances of SQL Server.

Listing 1: Using xp_readerrorlog to find the location of error log file

exec xp_readerrorlog 0,1,N'Logging SQL Server messages in file';

This example searches for the string Logging SQL Server messages in file in the active log file. The output shown in Figure 11 is returned when running the command.

Figure 11: Output from running code in Listing 1

The log record that identified the file location where the error log messages are being written can be found by searching for this particular string in the active log file.

Even though this stored procedure is undocumented, there are many resources out there that explain how to use it. This stored procedure supports seven parameters. Those parameters are described in Table 2.

Table 2: Parameters for xp_readerrorlog

Parameter	Description
1	Identifies the error log file that you would like to read.  Set this parm to 0 if you’d like to read the current error log.  Or you can set it to either 1, 2, 3, etc. to read one of the historical error log files.
2	Identifies which error log to search.  1, or null for ERRORLOG, or 2 for the SQL Agent log
3	The first string you want to search for in the error log file.
4	The second string you want to search for in the error log file.
5	The start time constraint on searching.
6	The end time constraint on searching.
7	Sort order of the output (ascending, descending)

Finding all the records in a large log file that contained the word error can easily be done by just changing the search string in parameter 3 of the code in Listing 1. You can write a short T-SQL script to find all the log records from the active SQL Server log file for yesterday and then place them in a temporary table for further analysis using the code in Listing 2.

-- Declare Variables needed
DECLARE @StartDate date,
        @EndDate   date;
-- Create temporary table to how error log records
CREATE TABLE #ErrorLogForYesterday (
  LogDate datetime, 
  ProcessInfo varchar(max), 
  Text varchar(max));
SET @StartDate = dateadd(dd,-1,getdate()); -- Yesterdays Date
Set @EndDate = getdate(); -- Todays Date
-- Extract error log records for yesterday in to temporary table 
INSERT INTO #ErrorLogForYesterday EXEC xp_readerrorlog 
            0,1,N'',N'',@StartDate,@EndDate;
-- Display error log records extracted
SELECT * FROM #ErrorLogForYesterday;
-- Cleanup
DROP TABLE #ErrorLogForYesterday;

Listing 2: Code to extract yesterday’s error log records

Programmatically finding error log records makes it easy to build processes to analyze the error log file. Using the method in Listing 2, a DBA could create a series of scripts that could programmatically run the xp_readerrorlog stored procedure to quickly analyze the different error log files.

Reading and Searching SQL Server Error Log Files

When SQL Server creates large error log files, it presents challenges for DBAs to read them. Large log files are cumbersome to scroll through to find errors. Luckily, the log view functionality of SSMS has the Filter and Search features built-in to allow a DBA to find strings within these large log files quickly. Additionally, using TSQL code to call the undocumented xp_readerrorlog stored procedure, allows a DBA to build scripts to read those large log files. Using these different methods to find errors in large SQL Server log files is critical for managing and maintaining SQL Server.

If you like this article, you might also like SQL Server Error Log Configuration – Simple Talk (red-gate.com)

The post Search SQL Server error log files appeared first on Simple Talk.

from Simple Talk https://ift.tt/3irgPBG
via