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.