Knowledge Base |

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

Article ID: 115519

Article Last Modified on 11/23/2005

APPLIES TO

- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 4.21a Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Workgroup Edition

This article was previously published under Q115519

## SUMMARY

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.

## MORE INFORMATION

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:

    table corrupt
    msg 605

Run Findstr.exe on the DBCC output files using this syntax:

    findstr /i /g:search.txt dbcc.* > findstr.out

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:

    The total number
    Table has
    Checking

Run Findstr on the DBCC output files using this syntax:

    findstr /v /i /g:search.txt dbcc.* > findstr.out

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:

    pages for object
    DBCC results
    DBCC execution completed

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.

    Copyright
    All rights reserved
    Logging SQL Server
    initconfig: number
    SQL Server is start
    initializing virtual
    Opening Master Data
    Loading SQL Server
    Recovering Database
    Recovery dbid
    transactions roll
    Activating disk
    initializing virtual
    server name is
    Clearing
    Using 'SQLEVENT.DLL
    Using 'OPENDSNT.DLL
    Using 'NTWDBLIB.DLL
    Using 'SSNMPNTW.DLL
    rolled forward
    default sort
    nocase
    default character
    recovery complete
    terminating due
    windows nt
    (id =
    killed by hostname
    17824
    17825
    1608
    17832
    OS error : 109
    OS error : 232
    unable to write to
    restrictions
    contracts
    server is unnamed
    no_log
    working thread
    Sybase
    network error
    united states
    unable to read login
    duplication
    subdivision
    clause
    number of buffers
    using asynchronous disk
    pipe name is
    number of proc buffers
    network information
    file descriptors
    transactions before ckpt
    shutdown by request

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:

    findstr /i /g:search.txt errorlog*.* | findstr
    /v /i /g:exclude.txt > f.out

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:

    findstr /i /g:search.txt errorlog*.* && batch1

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.

    echo off
    :start
    findstr /i /g:search.txt errorlog*.* | findstr
    /v /i /g:exclude.txt && (echo WARNING & batch1)
    goto start

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:
FINDSTR: Write error.
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:

    FOR /L %f IN (1, 1, 5) do isql /Usa /P /itest.sql /otest%f.out

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.

    findstr /s /i /c:"error.*605" errorlog*.* >  srch.out
    FOR /F %f IN (srch.out) do copy %f c:\results

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.

Additional query words: errorlogs Windows NT

Keywords: kbinfo kbusage KB115519