How to use the Findstr tool to scan SQL error log files or DBCC output files for analyzing errors

It is necessary to frequently examine DBCC output and the SQL Server error logs to check for problems. This can be an error-prone and laborious process, especially if you must do this on several servers, each of which may have multiple databases. Windows NT ships with a powerful pattern-searching utility called Findstr.exe that can be used to largely automate this task. You can also use this utility to conditionally execute a batch file based on whether certain error strings are found. This article describes how to use this Findstr.

Findstr is a high-performance pattern-searching utility that uses Win32 asynchronous and file-mapped I/O for best throughput. It allows the use of regular expressions, recursive searching down a directory tree, and the ability to exclude patterns, thus achieving a NOT function. See your Windows NT documentation for additional information.

There are two basic approaches to searching SQL Server error logs or DBCC output for errors using Findstr.exe:

• State explicitly what strings to find.
• State what strings you do not want to find (which will display all but these).

In the first approach, you build a list of strings that define errors you want Findstr to match. This list of strings can be supplied as an input file to Findstr.

For example, say you want to find all occurrences of the strings "table corrupt" (which accompanies many serious data errors) and "msg 605" (which is a specific type of error). Place these strings into a file so the content simply looks like this: Run Findstr.exe on the DBCC output files using this syntax: where search.txt is the file containing the search strings, dbcc.* matches a group of DBCC output files to search, and findstr.out is where the search results are to be placed.

The challenge with this approach is knowing in advance what error strings to search for. Unfortunately, there is no comprehensive list of SQL Server error strings that encompasses all possible DBCC, server, and kernel errors. For versions prior to 7.0, merely searching for these strings may provide fairly good coverage as a starting point: "table corrupt", "level 16"-"level 21", and "Severity: 16"-"Severity: 21."

Using a little experimentation, it is usually possible to further refine this and get fairly reliable checking (certainly more reliable than doing so by eye).

The other approach involves using the "reverse match" capability of Findstr to exclude a list of search strings. This approach is more easily applied to DBCC output than to error log output because there are fewer distinct strings that require exclusion. In some ways this approach, if applicable, is safer because you are only explicitly stating what not to match, and any new or unanticipated errors will be shown by default.

Because of the currently limited experience with error message frequency for version 7.0, the "reverse match" approach is probably the best method for this version.

For example, say you want to exclude the strings "The total number", "Table has", and "Checking" in a series of DBCC output files. This will exclude many non-essential DBCC status messages and will not result in loss of any errors. For versions before 7.0, place these strings into a file that looks like this: Run Findstr on the DBCC output files using this syntax: where search.txt is the file containing the search strings, dbcc.* matches a group of DBCC output files to search, and findstr.out is the destination for the search results. The /v parameter indicates "reverse match", which effectively matches all strings except those in the list.

For 7.0, do similarly but use these strings: The following is a sample exclusion list for error logs that will filter out non- essential messages and find most significant errors. This is not comprehensive and is intended as a sample only. Modify it to suit your needs. In some cases, combining these two approaches blends the best characteristics of both, or at least allows working around difficult problems that arise from using just one.

For example, say you are searching your error logs for the strings "Severity: 16" through "Severity: 21", which should match most serious errors. But you notice in your search output several communication- related errors such as 1608 and 17832, which are normally not indicative of a serious problem. You can keep your original search conditions and exclude only these specific unwanted errors from the search output by piping one Findstr command into another: where search.txt is the file containing the search strings, errorlog*.* are the SQL Server error log files to search, exclude.txt is the file containing the search strings to exclude, and f.out is where the search results are placed. This approach can be cascaded several levels deep, excluding successively more at each level.

It is possible to write a batch file that conditionally takes action based on whether Findstr detects a certain error string. This technique uses the Windows NT conditional processing commands which are "&&" and "||". The following command executes the batch file batch1.bat if any of the SQL Server error strings in the file search.txt are found: Batch1.bat could invoke a program or even run an ISQL job that uses the xp_sendmail capability of SQL Server to send a mail message, notifying an operator of a problem.

The techniques described above can be combined to perform a variety of tasks. For example, the following batch file will continuously run a Findstr command that conditionally executes batch1.bat if any of the SQL error strings in the file search.txt are found, excluding the strings in exclude.txt. With a little experimentation, you will find these techniques can be very helpful in reducing the labor involved in searching SQL Server error log and DBCC output files. These same techniques can also be used for any general text- searching task, not just those related to SQL Server.

Under some conditions, Findstr may encounter internal limits and be unable to complete a search. When this happens, you will see this message in the output: If this occurs, reduce the number of match strings you use, try a positive match instead of reverse match, or use a different search tool, such as Perl (more on this below).

A related topic is use of the Windows NT command-line tools. Starting with Windows NT 4.0, there are enhanced command extensions available. Do cmd /x /? to see the list. The enhanced FOR command is an example. Do for /? to see the syntax. Here is an example that shows how to use FOR to run isql 5 times in a row, saving each output to a differently named file: Below is an example of how to move all the files containing a certain error string to a common directory. This might be useful in culling out error-containing errorlogs from a large group of error-free ones so they can be sent to someone for further study. This illustrates how to execute a program for each file name in a file containing a list of names. In addition to command extensions, a version of Perl ships with the Windows NT Resource Kit. Perl is an interpreted C-like language highly optimized for file and string processing. It is easy to do complicated pattern matching and edit/replace operations with Perl. It can be used in conjunction with the Windows NT command line to provide very powerful features. There are many books and web information sources on Perl.