if exists (select * from sysobjects where id =
object_id(N'[dbo].[spKM_search_sqlerrorlog]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spKM_search_sqlerrorlog]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  OFF 
GO

CREATE proc  dbo.spKM_search_sqlerrorlog 

@lookfor varchar(25)= '', @LogNum char(2)= ''

  AS
/******************************

sql-scripting 04/04/2001 

Visit www.sql-scripting.com 
Edward J Pochinski III 

******************************/ 

/******************************

This script creates an sp that can be used to search the
SQL Error Log for a user specified search criteria.
This can be very handy when trying to find a particular
problem in very large SQL Error Logs.
I can not take full credit for this idea as
I first found the script on www.sql-scripting.com.
I have since modified  it to 
1) read the registry to determine 
where Your SQL install exists so that you do not have to tell
it where your SQL Error Logs reside;
2) accept a log number variable for searching offline error logs;
3) tell you if it does not find anything in the error log for the search
criteria you specified.

Phillip D. Snipes

*******************************/

DECLARE @SQLLogPath varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\MSSQLServer\Setup', @value_name='SQLDataRoot',
@value=@SQLLogPath OUTPUT
 

Declare @cmd varchar(65) 

/*********************
Print help message if no search criteria specified.
**********************/
     if @lookfor = '' 

Begin  

    Print '#################################################' 

   Print char(13)+'You must pass a string to search for.' 

   Print char(13)+'example: To search current SQL Error Log for
the word kernel'

Print char(13)+'exec spKM_search_sqlerrorlog kernel' 

   Print char(13)+'Optional parameter to pass is the Log
Number. The SQL Server starts a new' 

   Print char(13)+'Error Log each time it is started. To search
a previous SQL Error Log You must specify the Log Number.' 

Print char(13)+'If you do not pass a Log Number the
procedure will default to searching the current SQL Error Log.' 

Print char(13)+'If you want to search previous Logs, then
specify which Log to search by passing a number.' 

   Print char(13)+'example: To search the most recent previous
SQL Error Log for the word kernel'

Print char(13)+'exec spKM_search_sqlerrorlog kernel, 1' 

    Print
char(13)+'#################################################' 

  Return


End 

/********************
Defaults to searching current SQL Error Log if no LogNum specified.
*********************/
If @LogNum = ''
Begin
Set NoCount On
    set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" ' + @SQLLogPath +
'\Log\errorlog'
Create Table #Results ( Results varchar(8000))
insert into #Results
Exec xp_cmdshell @cmd
Select * from #Results
IF @@ROWCOUNT = 0 
BEGIN 
Print char(13)+'There are no entries in the
Current SQL Error Log matching the search criteria "'+@lookfor+'".'
End
Drop Table #Results
Set NoCount Off

End
/****************
Print which SQL Error Log file is being searched.
*****************/ 
If @LogNum > 0 And @LogNum < 7
Begin
If @logNum = 1 
Begin
Print char(13)+'SQL Error Log Archive #1'
End
If @logNum = 2 
Begin
Print char(13)+'SQL Error Log Archive #2'
End
If @logNum = 3 
Begin
Print char(13)+'SQL Error Log Archive #3'
End
If @logNum = 4 
Begin
Print char(13)+'SQL Error Log Archive #4'
End
If @logNum = 5 
Begin
Print char(13)+'SQL Error Log Archive #5'
End
If @logNum = 6 
Begin
Print char(13)+'SQL Error Log Archive #6'
End

Set NoCount On
set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" ' + @SQLLogPath +
'\Log\errorlog.' + @LogNum 
Create Table #Results1 ( Results varchar(8000))
insert into #Results1
Exec xp_cmdshell @cmd
Select * from #Results1
IF @@ROWCOUNT = 0 
BEGIN 
Print char(13)+'There are no entries in the
SQL Error Log Archive #'+@LogNum+' matching the search criteria
"'+@lookfor+'".'
End
Drop Table #Results1
Set NoCount Off
End

/*************************
Prinnt an error message if the LogNum specified is too high.
**************************/
If @logNum > 6 
Begin
Print char(13)+'SQL Error Log Number out of
Range. MS SQL Server does not maintain more than 6 Archived Error Logs.'
End