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