Microsoft KB Archive/115519

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.