I created a stored procedure, sp_ShowErrors, to view SQL
Server errorlog files. In its default configuration, this stored
procedure takes the two most recent errorlog files and produces a report that highlights errorlog entries of interest. To
give context to those errorlog entries, the report also includes
(but doesn't highlight) the preceding and following four
entries in the errorlog file.
This stored procedure has saved me countless hours of
tedium over the years. For example, by adding sp_ShowErrors to a batch process that runs on more than 40 servers and
concatenating the results to one file, I can review the errorlog
files for all the servers in a matter of minutes. It's a great alternative to logging on to each server and manually reviewing
the errorlog files through the GUI or not even reviewing
them at all due to time constraints. I have performance-tuned
sp_ShowErrors so that it can handle even the largest errorlog
file that would cause Enterprise Manager to choke.
Listing 1 shows an excerpt from sp_ShowErrors.
(You can download the entire stored procedure from the SQL Server Web site.)
As callout A shows, sp_ShowErrors uses the sp_readerrorlog stored procedure.
Note that sp_readerrorlog is undocumented, so you need to use at your own risk.
I wrote sp_ShowErrors for SQL Server 2000. To get it working in SQL Server
2005, you'll need to modify the code because the output of sp_readerrorlog in
SQL Server 2005 is different. I hope you find this stored procedure as useful
as I do.
—Bill McEvoy
End of Article
