{"id":89608,"date":"2021-01-19T19:58:09","date_gmt":"2021-01-19T19:58:09","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89608"},"modified":"2022-04-24T16:10:24","modified_gmt":"2022-04-24T16:10:24","slug":"search-sql-server-error-log-files","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/search-sql-server-error-log-files\/","title":{"rendered":"Search SQL Server error log files"},"content":{"rendered":"

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\u2019t even be opened up using SSMS. This article will show you a few different ways to browse and search SQL Server error log files.<\/p>\n

Using SSMS to search and filter large SQL Server error log files<\/h2>\n

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<\/em> and filter<\/em> options to find the information in large error log files. I\u2019ll demonstrate how to use these options to find information in large error log files.<\/p>\n

Using the search option<\/h3>\n

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.<\/p>\n

\"Browsing<\/p>\n

Figure 1: Browsing my error log file<\/strong><\/p>\n

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<\/em> 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.<\/p>\n

\"Search<\/p>\n

Figure 2: Search selection dialog<\/strong><\/p>\n

To search, just enter the string of characters you want to find in the Search for:<\/em> field. The characters can be case-insensitive or case-sensitive based on whether the Match case<\/em> check box is checked. You can also search just the Message column or all the columns depending on if the Search Message column only<\/em> 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.<\/p>\n

For this demonstration, enter the string error<\/em> in the Search for:<\/em> criteria. Once the search criteria are filled in, the Search<\/em> button is enabled, as shown in Figure 3.<\/p>\n

\"Enabling<\/p>\n

Figure 3: Enabling Search Button<\/strong><\/p>\n

When clicking the Search<\/em> button, the error log position is relocated to the first occurrence of the string error<\/em>, as shown in Figure 4.<\/p>\n

\"Repositioned<\/p>\n

Figure 4: Repositioned to first occurrence of the string<\/strong><\/p>\n

Click the Search<\/em> button again to move to the next message text that contains the string error<\/em>, as shown in Figure 5.<\/p>\n

\"Next<\/p>\n

Figure 5: Next occurrence of the string \u201cerror\u201d<\/strong><\/p>\n

By reviewing Figure 5, you can see the search function found the string error<\/em> just a few lines down further in the log (the actual string error<\/em> 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<\/em>. Once the last message is found, the search will start over from the top if you click the button again.<\/p>\n

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

Using the Filter Option<\/h3>\n

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<\/em> options in the Log File Viewer<\/em> window, as shown in Figure 6.<\/p>\n

\"Selecting<\/p>\n

Figure 6: Selecting the Filter Option<\/strong><\/p>\n

When the filter option is clicked, the dialog box in Figure 7 is shown.<\/p>\n

\"Filter<\/p>\n

Figure 7: Filter Options<\/strong><\/p>\n

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.<\/p>\n

Table 1: Descriptions for each filter option<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n
\n

Filter Name<\/strong><\/p>\n<\/td>\n

\n

Description<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

User<\/p>\n<\/td>\n

\n

The user name that is associated with the log entry<\/p>\n<\/td>\n<\/tr>\n

\n

Computer<\/p>\n<\/td>\n

\n

The computer that is associated with the log entry<\/p>\n<\/td>\n<\/tr>\n

\n

Start Date<\/p>\n<\/td>\n

\n

Log entry must be created on or after this date<\/p>\n<\/td>\n<\/tr>\n

\n

End Date<\/p>\n<\/td>\n

\n

Log entry must be created on or before this date<\/p>\n<\/td>\n<\/tr>\n

\n

Message contains text<\/p>\n<\/td>\n

\n

Log entry message must contain this text (case-insensitive)<\/p>\n<\/td>\n<\/tr>\n

\n

Source<\/p>\n<\/td>\n

\n

The source of the log entry<\/p>\n<\/td>\n<\/tr>\n

\n

Instance Name<\/p>\n<\/td>\n

\n

The instance Name that is associated with the log entry<\/p>\n<\/td>\n<\/tr>\n

\n

Event<\/p>\n<\/td>\n

\n

The event id that is associated with the windows log entry<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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

\"Applying<\/p>\n

Figure 8: Applying Filter<\/strong><\/p>\n

After clicking the OK<\/em> button, only the error log lines that contain the text are displayed, as shown in Figure 9. If the Apply Filter<\/em> checkbox is not checked, before clicking on the OK<\/em> button, the filter won\u2019t be applied.<\/p>\n

<\/p>\n

Figure 9: Results of message text filter<\/strong><\/p>\n

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<\/em> and End Date<\/em> 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.<\/p>\n

Out of memory errors when viewing large logs<\/h2>\n

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.<\/p>\n

<\/p>\n

Figure 10: Out of memory exception when trying to view a large error log file<\/strong><\/p>\n

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.<\/p>\n

If you are trying to use the SSMS to view large log files and having memory issues, this doesn\u2019t mean you are out of luck. There are other options to view, search and filter these large log files.<\/p>\n

Using a text editor to view a large log file<\/h2>\n

One option to view a large log file is to use a text editor. But it can\u2019t 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\u2019m 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.<\/p>\n

Programmatically searching the error log file<\/h2>\n

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

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.<\/p>\n

Listing 1: Using xp_readerrorlog to find the location of error log file<\/strong><\/p>\n

exec xp_readerrorlog 0,1,N'Logging SQL Server messages in file';<\/pre>\n
This example searches for the string Logging SQL Server messages in file<\/em> in the active log file. The output shown in Figure 11 is returned when running the command.<\/p>\n
<\/p>\n
Figure 11: Output from running code in Listing 1<\/strong><\/p>\n
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.<\/p>\n
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.<\/p>\n
Table 2: Parameters for xp_readerrorlog <\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
Parameter<\/strong><\/p>\n<\/td>\n
\n
Description<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
Identifies the error log file that you would like to read.\u00a0 Set this parm to 0 if you\u2019d like to read the current error log.\u00a0 Or you can set it to either 1, 2, 3, etc. to read one of the historical error log files.<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
Identifies which error log to search.\u00a0 1, or null for ERRORLOG, or 2 for the SQL Agent log<\/p>\n<\/td>\n<\/tr>\n
\n
3<\/p>\n<\/td>\n
\n
The first string you want to search for in the error log file.<\/p>\n<\/td>\n<\/tr>\n
\n
4<\/p>\n<\/td>\n
\n
The second string you want to search for in the error log file.<\/p>\n<\/td>\n<\/tr>\n
\n
5<\/p>\n<\/td>\n
\n
The start time constraint on searching.<\/p>\n<\/td>\n<\/tr>\n
\n
6<\/p>\n<\/td>\n
\n
The end time constraint on searching.\u00a0<\/p>\n<\/td>\n<\/tr>\n
\n
7<\/p>\n<\/td>\n
\n
Sort order of the output (ascending, descending)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Finding all the records in a large log file that contained the word error<\/em> 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.<\/p>\n
-- Declare Variables needed\r\nDECLARE @StartDate date,\r\n        @EndDate   date;\r\n-- Create temporary table to how error log records\r\nCREATE TABLE #ErrorLogForYesterday (\r\n  LogDate datetime, \r\n  ProcessInfo varchar(max), \r\n  Text varchar(max));\r\nSET @StartDate = dateadd(dd,-1,getdate()); -- Yesterdays Date\r\nSet @EndDate = getdate(); -- Todays Date\r\n-- Extract error log records for yesterday in to temporary table \r\nINSERT INTO #ErrorLogForYesterday EXEC xp_readerrorlog \r\n            0,1,N'',N'',@StartDate,@EndDate;\r\n-- Display error log records extracted\r\nSELECT * FROM #ErrorLogForYesterday;\r\n-- Cleanup\r\nDROP TABLE #ErrorLogForYesterday;<\/pre>\n
Listing 2: Code to extract yesterday\u2019s error log records<\/strong><\/p>\n
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.<\/p>\n
Reading and Searching SQL Server Error Log Files<\/h2>\n
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<\/em> and Search<\/em> 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<\/code> 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.<\/p>\n
If you like this article, you might also like<\/em>\u00a0SQL Server Error Log Configuration – Simple Talk (red-gate.com)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
The SQL Server error log is helpful for troubleshooting issues. Greg Larsen demonstrates several ways to search SQL Server error log files.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[136045,136047,5842,136046],"coauthors":[11330],"class_list":["post-89608","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-error-log","tag-search-sql-server-erro-r-log","tag-sql-monitor","tag-sql-server-error-log"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89608","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89608"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89608\/revisions"}],"predecessor-version":[{"id":89621,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89608\/revisions\/89621"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89608"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}