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.