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

  - Microsoft SQL Server, version 4.2

SUMMARY

It is necessary to frequently examine DBCC output and the SQL error logs to check for problems. This can be an error-prone or 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, which can be used to greatly automate this task. It is even possible to conditionally execute a batch file based on whether certain error strings are found. This article describes how to do this, including examples.

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 more information.

There are two basic approaches to searching SQL error logs or DBCC output for errors:

   1. State explicitly what strings to find.

   2. State what strings you do not want to find, which will show you
      all but these.

For example, say you want to find any occurrences of these strings "table corrupt" (which accompanies many serious data errors), and "msg 605" (which is a specific type of error). Place into a file these strings, so the content simply looks like this:

   table corrupt
   msg 605

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

The challenge with this approach is knowing ahead of time what error strings to search for. Unfortunately, there is no comprehensive list of SQL error strings which encompasses all possible DBCC, server, and kernel errors. However, merely searching for these strings may provide fairly good coverage as a starting point: "table corrupt", "level 16", "level 17"..."level 21", "Severity: 16", "Severity: 17"..."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, since there are fewer distinct strings which require exclusion. In some ways this approach, if applicable, is safer since you are only explicitly stating what NOT to match, and any new or unanticipated errors will be shown by default.

For example, say you want to exclude all strings in a series of DBCC output files which contain the strings "The total number", "Table has", and "checking". Study indicates this will exclude many non-essential DBCC status messages, and will not result in loss of any errors. Place into a file these strings, so that the contents simply looks like this:

   The total number
   Table has
   checking

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

Here is a sample exclusion list for error logs, which will filter out non- essential messages and let through most significant errors.

   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

For example, say you are searching your error logs for the strings "Severity: 16", "Severity: 17"..."Severity: 21", which should match most serious errors. But you notice in your search output several communication- related errors such as 1608 and 17832 that 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

It is possible to write a batch file which 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 error strings in the file SEARCH.TXT are found:

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

The above techniques 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