SQL SERVER – How to Suppress SQL Server messages in Application Event Log

Error messages are a great source of help when it comes to troubleshooting. What can I say, this is maybe be the most repeated phrase from me in this blog about error messages. The messages have grown over ages and got better with the SQL Server dev team putting in extra effort in every release. Having said that, I thought of adding something interesting in this blog.

Logging of errors in ERRORLOG is helpful in many situations. It has helped me many time to fix issues. Recently I came across an email where there was a strange behavior reported. Here in the email from my friend.

Hi Pinal,
We are using 3^rd party software which uses SQL Server as backend. We also have monitoring software which keeps track of messages in Event log and sends alert to Wintel team.
Recently there was a brute force attach for sa password on that server so there were many login failure attempts like below:

2015-11-05 20:14:47.040 Logon        Error: 18456, Severity: 14, State: 8.

2015-11-05 20:14:47.040 Logon        Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: 10.10.10.20]

There messages were logged many time and IP address listed there is some machine which we don’t know. We were able to stop the connection by firewall.

Here is the problem: Monitoring software didn’t report anything because strangely these messages were NOT logged in application event log at all.

Do you know how that can happen?
Thanks in advance.

Whenever I get such questions, I always asked for ERRORLOG.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

I looked into ERRORLOG file and found something interesting as below.

Registry startup parameters:

-d D:\MSSQL12.SQL2014\MSSQL\DATA\master.mdf

-e D:\MSSQL12.SQL2014\MSSQL\Log\ERRORLOG

-l D:\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf

-n

Command Line Startup Parameters:

-s "SQL2014"

We can see a non-default startup parameter called “n”. I searched MSDN documentation and found its usage. “Does not use the Windows application log to record SQL Server events”

So I replied to my friend and later it was identified that 3^rd party tool has done that change for that instance.

Mystery solved and I learned something new.  Have you ever used any such non-default startup parameter in production server?

Reference: Pinal Dave (https://blog.sqlauthority.com)