SQL Server Error Log Check

19 March,2007 by Jack Vamvas

Returns records from the SQL Server error log based on search terms. Integrating with Powershell , makes automation easier check Powershell and SQL Error Logs 

DECLARE @SERVER NVARCHAR(50)

SET @SERVER = 'SERVER1\INSTANCE'

DECLARE @sqlStatement1 VARCHAR(200)

SET @sqlStatement1 = @SERVER + '.master.dbo.xp_readerrorlog'

CREATE TABLE #Errors (vchMessage varchar(2000), ID int)

--CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC @sqlStatement1

--select * from #errors

--INSERT INTO TBL_MONITOR_SQL_ERROR_LOG(server,dbName,logMessage,capturedDate)

SELECT @server,'na',RTRIM(LTRIM(vchMessage)),GETDATE() FROM #Errors WHERE

([vchMessage] like '%error%'

or [vchMessage] like '%fail%'

or [vchMessage] like '%Warning%'

or [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'

or [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'

or [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'

or [vchMessage] like '% is full%'

or [vchMessage] like '% blocking processes%'

or [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'

)

and [vchMessage] not like '%\ERRORLOG%'

and [vchMessage] not like '%Attempting to cycle errorlog%'

and [vchMessage] not like '%Errorlog has been reinitialized.%'

and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'

and [vchMessage] not like '%without errors%'

and [vchMessage] not like '%This is an informational message%'

and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'

and [vchMessage] not like '%The error log has been reinitialized%'

and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'

and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'

and [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'
 

DROP TABLE #Errors

Related Posts 

Powershell and SQL Error Logs

SQL Server – Error Logs recycle without SQL Server Restart


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Error Log Check


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer