SQL Server – Error Log – Parsing – FindStr

Background

A need to parse all available SQL Server Error Logs for a SQL Server Instance and find I/O issues.

Code

Stored Procedure – dbo.sp_errorLog_findstr




use [master]
go

if object_id('dbo.sp_errorLog_findstr') is null
begin

	exec('create procedure [dbo].[sp_errorLog_findstr] as ')

end
go

alter procedure [dbo].[sp_errorLog_findstr]
(
	  @stringSought	varchar(255)
	, @stringSought2	varchar(255) = null
)
as

begin

	declare @FileID			int
	declare @FileMaxID		int
	declare @Subsystem		int

	SET NOCOUNT ON

	DECLARE @maxLog      INT
	DECLARE @searchStr   VARCHAR(256)

	DECLARE @errorLogs   TABLE 
	(
		  LogID    INT
		, LogDate  DATETIME
		, LogSize  BIGINT   
	);

	DECLARE @logData  TABLE 
	(
		  FileID   INT default (-1) NULL
		, LogDate     DATETIME
		, ProcInfo    VARCHAR(64)
		, LogText     VARCHAR(MAX)   
	);

	INSERT INTO @errorLogs
	(
		  LogID 
		, LogDate
		, LogSize
	)
	EXEC sys.sp_enumerrorlogs
	;

	SET @FileID = 0
	set @Subsystem = 1

	SELECT @FileMaxID = max([LogID])
	FROM   @errorLogs

	WHILE (@FileID <= @FileMaxID )
	BEGIN

		INSERT INTO @logData
		(
			  [LogDate] 
			, [ProcInfo]
			, [LogText]
		)
		EXEC sys.sp_readerrorlog 
				  @FileID
				, 1
				, @stringSought 
				, @stringSought2
				;

		update  tblLD
		set     tblLD.FileID = @FileID
		from    @logData tblLD
		where   (
					   ( tblLD.FileID is null )
					or ( tblLD.FileID = -1 )
				)

		SET @FileID = @FileID + 1

	END

	SELECT FileID, [LogDate], [LogText]
	FROM @logData
	ORDER BY
			 [LogDate] desc
			;

end
go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_errorLog_findstr]'
go




Sample


	declare @stringSought varchar(255)
	declare @stringSought2 varchar(255)

	set @stringSought  = 'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file'
	set @stringSought2 = 'tempdb'

	exec [dbo].[sp_errorLog_findstr]
		  @stringSought = @stringSought
		, @stringSought2 = @stringSought2




Output

sqlserver-error-iostalls-20170117-0300pm

 

Explanation

  1. Part of the problem in our case is that all files are using our system drive ( Drive C:)

 

Source Code Repository

GitHub

Placed here

 

Referenced Work

Here are the code that I used:

  1. Mike Eastland
    • Search multiple SQL Server Error Logs at the same time
      Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s