As part of troubleshooting, it is necessary to frequently examine the SQL Server error log and DBCC output 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.
Use DBCC with the WITH NO_INFOMSGS option to isolate error messages in the error log. Informational messages (severity levels 0 through 10) are excluded from the check.
To make this examination process easier, you can use the Windows NT findstr utility (FINDSTR.EXE). This powerful pattern-searching utility helps to automate troubleshooting. It is even possible to conditionally execute a batch file based on whether certain error strings are found.
The findstr utility uses Win32 asynchronous and file-mapped I/O for best throughput. It allows the use of regular expressions, recursive searching through a directory tree, and the ability to exclude patterns (achieving a NOT function). For details, see your documentation for Windows NT.
There are two basic approaches to searching the SQL Server error log or DBCC output for errors:
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, 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 search.txt file. The content looks like this:
table corrupt msg 605
Run findstr on the DBCC output files using this syntax:
findstr /i /g:search.txt dbcc.* > findstr.out
where
The challenge with this approach is knowing ahead of time which error strings to search for. Unfortunately, there is no comprehensive list of SQL Server error strings that encompasses all possible errors. However, searching for the following strings may provide fairly good coverage as a starting point: "table corrupt," "level 16" ... "level 21," "Severity: 16" ... "Severity: 21." It is usually possible to refine the search, resulting in reliable checking (certainly more reliable than doing a visual search).
In the second approach. you build a list of strings that you do not want to find (in other words, exclude these strings from the search).
This sample exclusion list for error logs will filter out nonessential messages and trap the 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 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 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, to run findstr on the error log and have the above text saved as SEARCH.TXT, use:
run findstr /v /i /g:search.txt errorlog *.* > f.out
In some cases, combining the inclusion and exclusion approaches works well, combining the best characteristics of each.
For example, when searching error logs for the strings "Severity: 16" ... "Severity: 21," which should match most serious errors, you notice in the search output several communication-related errors that normally do not indicate a serious problem. To refine the search, keep the 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
This approach can be cascaded several levels deep, excluding successively more at each level.