Error log filter.... in a different sense

Question

Post reply

Error log filter.... in a different sense

gtepper

Newbie

Points: 3
More actions
September 13, 2011 at 9:22 am

#257000

Greetings,
I want to be able to filter our error log, but not in the typical way that I have seen through searching on the web.
I can see how one can log into SSMS and view the error log and click on filter to do a search for a certain string, date, user, etc.
That is fine, but what if I have a stack trace error that gets logged every morning that I do not want to appear in the log at all. So I would like to apply a filter that will not even let a certain string make it to the log file.
Does anyone know how this can be done?
Thanks you in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

This is how I do it:

---------------------

--Following block parses error log and produces interesting results

----------------------

CREATE TABLE #ErrorLog (

LogDate DATETIME,

ProcessInfo NVARCHAR(255),

LogText NVARCHAR(MAX)

);

GO

INSERT INTO #ErrorLog (

[LogDate],

[ProcessInfo],

[LogText]

)

EXEC xp_readerrorlog

--select * from #ErrorLog

--where ProcessInfo !='Logon'

--and LogText not like 'Database backed up%'

select * from #ErrorLog

where ProcessInfo !='Logon'

and LogText not like 'Database backed up%'

and LogText not like 'Log was backed up%'

and LogText not like '%found 0 errors%'

--and LogText not like 'This instance of SQL Server has been using a process ID%'

and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

and LogText not like 'Microsoft SQL Server 200%'

and LogText not like '(c) %'

and LogText not like 'All rights rese%'

and LogText not like 'Server process ID is%'

and LogText not like 'System Manufacturer:%'

and LogText not like 'Authentication mode is %'

and LogText not like 'Logging SQL Server messages in file%'

--and LogText not like 'This instance of SQL Server last reported using a process ID o%'

and LogText not like 'Registry startup parameters:%'

and LogText not like 'SQL Server is starting at normal priority base%'

and LogText not like 'Detected % CPUs. This is an informational messag%'

and LogText not like 'Using locked pages for buffer pool%'

and LogText not like 'Using dynamic lock allocation.%'

and LogText not like 'Node configuration: node 0: CPU mask%'

and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

and LogText not like 'Server is listening on %'

and LogText not like 'Server local connection provider is ready to accept connection on%'

and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

and LogText not like 'Service Broker manager has started.'

and LogText not like 'Starting up database%'

and LogText not like 'CHECKDB for database % finished without errors on %'

and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

and LogText not like 'AppDomain % unloaded.'

--change the 72 below to alter timeframe of log read.

and LogDate> DATEADD(hh, - 72, GETDATE())

drop table #ErrorLog

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

ah, just reread your post... this will not PREVENT something frmo being entered into the log, but I used this in a multiserver query to read the logs and return only results that I'm interested in... thinking about your actual question still...

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

I can't think of a way of preventing this kind of error from appearing in the errorlog, but maybe there is another way to accomplish your goal.

The best thing I can think of is prevening the stack trace error from occuring.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

I don't think there is a way to prevent the logging of those events..

Thank You,

Best Regards,

SQLBuddy