INF: How to Scan SQL Errorlog or DBCC Output for Errors

ID: Q115519

The information in this article applies to:

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:

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          : kbusage SSrvErr_Log kbSQLServ600 kbSQLServ650 kbSQLServ700 SSrvWinNT 
Version           : winnt:4.2x,6.0,6.5,7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: April 10, 1999