March 9, 2018 at 12:04 pm
Hi All,
Is there a way to prevent or redirect SQL Server error logs from writing to the Application Event Log found in even viewer? We would like to use the Application Event Log for non-SQL Server errors only because they are bloating with all the SQL Server error logging information and we already have those in the SQL Server Error Logs so we don't' need them in both places...
I can't find anything online except the below article which implies the behavior to write to the Windows Application log can be configured.
https://docs.microsoft.com/en-us/sql/relational-databases/performance/view-the-windows-application-log-windows-10
When SQL Server is configured to use the Windows application log, each SQL Server session writes new events to that log. Unlike the SQL Server error log, a new application log is not created each time you start an instance of SQL Server.
March 9, 2018 at 12:50 pm
Startup option -n
Does not use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, we recommend that you also use the -e startup option. Otherwise, SQL Server events are not logged.
ahenderson - Friday, March 9, 2018 12:04 PMwe don't' need them in both places...
Not trying to sound like a dick, but I have to follow this up with "until you do". By default SQL only keeps 6 backup logs before recycling them. It wouldn't be unheard of to have an issue that resulted in the service being cycled enough times that you might lose the log containing the initial error. Also, filtering and finding issues in the event viewer is a lot easier than looking through the log files in a text editor or something else if SQL isn't reachable (in my opinion).
March 20, 2018 at 12:16 pm
SQLPirate - Friday, March 9, 2018 12:50 PMStartup option -nDoes not use the Windows application log to record SQL Server events. If you start an instance of SQL Server with -n, we recommend that you also use the -e startup option. Otherwise, SQL Server events are not logged.
ahenderson - Friday, March 9, 2018 12:04 PMwe don't' need them in both places...
Not trying to sound like a dick, but I have to follow this up with "until you do". By default SQL only keeps 6 backup logs before recycling them. It wouldn't be unheard of to have an issue that resulted in the service being cycled enough times that you might lose the log containing the initial error. Also, filtering and finding issues in the event viewer is a lot easier than looking through the log files in a text editor or something else if SQL isn't reachable (in my opinion).
Awesome. Thank you for the response SQLPirate.
Just to explain the situation, we have the option to log all successful logins as well as failed logins. This makes our error logs quite large very fast for busy SQL Servers. Typically our error logs are configured for 14 days or 14 recycles which lasts much longer than the Application event log. The server in question only keeps less than an hour of information because of the bloat on the Applciation event log which renders it useless for the most part. There is always an option to increase the size of the Appilcation event log, but even then, the amount of successful logins that are logged in it makes it extremely hard to investigate for other issues.
March 21, 2018 at 3:53 pm
maybe this will work for you:
---
--- get successful logon messages
---
use master
go
select *
from sys.sysmessages
where msglangid = 1033
and description like '%login%succeed%'
go
error severity dlevel description msglangid
----------- -------- ------ ------------------------------------------------------------------------------------------------ ---------
18453 10 128 Login succeeded for user '%.*ls'. Connection made using Windows authentication.%.*ls 1033
18454 10 128 Login succeeded for user '%.*ls'. Connection made using SQL Server authentication.%.*ls 1033
18455 10 128 Login succeeded for user '%.*ls'.%.*ls 1033
28046 10 128 %S_MSG Login succeeded for user '%.*ls'. Authentication mode: %.*ls. %.*ls 1033
(4 row(s) affected)
---
--- this might work
---
exec sp_altermessage @message_id=18453, @parameter='write_to_log', @parameter_value='false'
exec sp_altermessage @message_id=18454, @parameter='write_to_log', @parameter_value='false'
exec sp_altermessage @message_id=18455, @parameter='write_to_log', @parameter_value='false'
exec sp_altermessage @message_id=28046, @parameter='write_to_log', @parameter_value='false'
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply