Sqlserver-dba.com - SQL Server support and SQL Server Performance Tuning
    SQL Server DBA:Everything | DBA Scripts | Powershell Scripts | DBA checklists | SQL Antipattern | Wait Stats | SQL Server Performance Tuning

Search sqlserver-dba.com

Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

Recent Posts

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 @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: Tom Collins (http://www.sqlserver-dba.com)


Share:

Posted by Tom Collins at 10:08 AM in DBA Scripts, Monitor Performance, SQL Error Logs, SQL Server Error Tips | Permalink | Comments (2)

Searches: , , , , ,

-- updated 05/18/2020 just for fun --

DECLARE @SERVER NVARCHAR(100)

SET @SERVER = 'WATVW-DBWEB02'

DECLARE @sqlStatement1 VARCHAR(500)

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

CREATE TABLE #Errors ([logdate] varchar (100),[processinfo] nvarchar (20), vchMessage varchar(2000))

INSERT #Errors EXEC @sqlStatement1

SELECT RTRIM(LTRIM(vchMessage)) as [worthyerror],GETDATE() as [today] 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

Posted by: Scotty | May 19, 2020 9:27:02 AM

-- updated 05/18/2020 just for fun " sorry got carried away, lol --

DECLARE @SERVER NVARCHAR(100)

SET @SERVER = 'SERVER1\INSTANCE'

DECLARE @sqlStatement1 VARCHAR(500)

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

CREATE TABLE #Errors ([logdate] varchar (100),[processinfo] nvarchar (20), vchMessage varchar(2000))

INSERT #Errors EXEC @sqlStatement1

SELECT RTRIM(LTRIM(vchMessage)) as [worthyerror],GETDATE() as [today] 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

Posted by: scotty | May 19, 2020 9:32:45 AM

Verify your Comment

Previewing your Comment

Posted by: |

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 How to check SQL error logs in SQL Server?

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