Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to check SQL error logs in SQL Server?

19 March,2007 by Tom Collins

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 @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.'


Related Posts 

Powershell and SQL Error Logs

SQL Server – Error Logs recycle without SQL Server Restart

Author: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on How to check SQL error logs in SQL Server? | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer