Hello experts,
I am running into an issue where log information in the Windows Application event log gets rotated out too quickly. Our Systems team tells me this is because the SQL Server in question is set to log both successful and failed logins. And it is true that I see tons and tons of successful logins that seem to happen when all the various applications connect, reconnect, etc.
Because the logs get rotated after they reach a certain size, after about 24 hours some other event is likely to no longer be in the log because it got exported and sent to Sumo logic (which is insanely hard for me to query, full disclosure). This makes it tough to find non-login events that may be needed for troubleshooting.
So I guess my first question is, is there a way to configure SQL Server to send successful logins to a separate Windows event log, so that they don't fill up the Application event log?
But my second question is, am I asking the right question? Is there some other way to resolve this issue?
Thanks for any help!
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Instead of enabling both successful and failed logins - you can configure an audit instead. The audit can also send the data to the application event log - but that should keep it out of the SQL Server logs so you can troubleshoot SQL Server issues.
Here is one article: https://www.mssqltips.com/sqlservertip/5154/audit-sql-server-logins-without-filling-up-the-error-log/
I also found this one: https://www.kodyaz.com/t-sql/sql-server-login-auditing.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2022 at 11:27 pm
Instead of enabling both successful and failed logins - you can configure an audit instead. The audit can also send the data to the application event log - but that should keep it out of the SQL Server logs so you can troubleshoot SQL Server issues.
Here is one article: https://www.mssqltips.com/sqlservertip/5154/audit-sql-server-logins-without-filling-up-the-error-log/
I also found this one: https://www.kodyaz.com/t-sql/sql-server-login-auditing.aspx
the OP issue is not the error log but the event log itself getting recycle too fast due to the number of SQL logins getting logged onto it
December 23, 2022 at 2:13 pm
Thanks Jeffrey, Frederico,
This information is very helpful. I will look into the audits as well as possibly trying to negotiate (unlikely but can't hurt to ask) with the Systems team about the log recycling interval.
Thanks again.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 23, 2022 at 5:24 pm
Jeffrey Williams wrote:Instead of enabling both successful and failed logins - you can configure an audit instead. The audit can also send the data to the application event log - but that should keep it out of the SQL Server logs so you can troubleshoot SQL Server issues.
Here is one article: https://www.mssqltips.com/sqlservertip/5154/audit-sql-server-logins-without-filling-up-the-error-log/
I also found this one: https://www.kodyaz.com/t-sql/sql-server-login-auditing.aspx
the OP issue is not the error log but the event log itself getting recycle too fast due to the number of SQL logins getting logged onto it
By moving over to an Audit - instead of enabling through the standard configuration, you eliminate those entries in the SQL logs. This allows you to search the SQL logs for any issues without having to weed through all of those login records.
If the goal is to be able to search the application event log for server related issues - then you can move the logins to the security log or a separate file.
From a DBA perspective - eliminating the entries from the SQL logs is ideal because we don't normally check application event logs for SQL Server related issues.
In this case, I would recommend using an audit and directing the audit to the security log. Note: that does require some additional permissions be setup and configured - but once that is done the entries would be sent to the security log and forwarded to Sumo where that product could be used as intended.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2022 at 7:07 am
The size of a Windows event log is configurable. If the log is cycling too quickly, then one solution is to make it larger.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply