Prevent SQL Server error logs from writing to Windows Event Application log?

  • 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.

  • 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.

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options

    ahenderson - Friday, March 9, 2018 12:04 PM

     we 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).

  • SQLPirate - Friday, March 9, 2018 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.

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options

    ahenderson - Friday, March 9, 2018 12:04 PM

     we 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.

  • 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