In this article, we will explore the method to suppress successful login and backups messages in the error log for SQL databases.
Introduction
SQL Server logs useful event information in the error log. It is a primary point of investigating any possible issues with the SQL Server, database, connections, backups, trace flags, startup parameters, recovery information. It helps to minimize the entries in this log file so that you can easily find out the relevant data. One option is to recycle the error log at frequent intervals so that SQL Server can process the information in a log file quickly. You should do the error log recycle as part of your SQL database maintenance activities.
Usually, SQL Server error logs filled with two kinds of events.
Login success messages
Whenever a new connection occurs in SQL Server, it logs an entry in the error log. In a busy OLTP system, you might get much connection every second, and it might fill your error log. Many times, we do not require these events in our error log. By default, SQL Server logs both successful and failed login messages in the error log. It is part of login auditing in SQL Server.
To view the current login audit for your SQL instance, connect to it in SSMS and open instance properties. In the instance properties, navigate to Security and check for the login auditing option as shown below.
In the below image, we see both successful and failed logins.
Usually, we do not require these successful logins information. We only require the failed login messages so that if any user complaints about connection issues, we can check the error message, its severity, and state to troubleshoot further.
It is a good idea to suppress these login success messages in the error logs. You can configure an extended event session in case you need to capture this information for some regulatory requirements
Disable successful login messages in the error log
To disable the successful login messages in the SQL Server error log, modify the login auditing option to Failed logins only.
SQL Server stores the login auditing information in the registry. If you script out the SSMS actions, you see that it modifies the registry key value to control the SQL Server login auditing mechanism.
|
1 2 3 4 |
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2 GO |
It immediately changes the login auditing in SQL Server. You do not require to recycle SQL Server services. After the change, you only get failed connections information.
Backup success messages
SQL Server logs information about each successful backups. For a production instance, we might combine full, differential and transaction log backups. The transaction log backup might frequently occur such as every 5 or 10 minutes depending upon your recovery objectives. You get entries in the error logs like below for full and transaction log backup. Sometimes, you might see timeout errors while you open the error log in the SSMS. It might occur due to large error logs.
Suppose in a production instance, you have 100 databases, and your transaction log backup occurs every 5 minutes. It means every 5 minutes you have 100 entries for the transaction log backups. It means 1200 entries in an hour and 28,800 entries in a day. It fills your transaction log quickly. Really! You might also miss some useful events due to these bulk entries. It is applicable to log shipping as well that takes regular SQL database backup and restore on to the secondary instance.
SQL database professionals do not monitor the backups using these error logs. We can query system tables in the MSDB database to check backup history.
Turn on trace flag 3226 at global-session level
To suppress the successful backup entries, we can use the trace flag 3226. Trace flags in SQL Server are like unique configurations that alter specific system actions. We can enable the trace flags at the system level or the session-level. You can learn more about trace flags in the article SQL Server 2016 Trace flags modifications.
Let’s enable this trace flag at the session level and observer the transaction log entries in the error logs. Before we enable the trace flag, recycle the error log using the sp_cycle_errorlog system stored procedure. It closes the existing error log file and creates a new file similar to the SQL service restart process.
We use DBCC TRACEON to enable a specific trace flag. The -1 argument tells SQL Server to enable the trace flags at the global session-level.
|
1 2 3 4 |
Exec sp_cycle_errorlog Go DBCC TRACEON(3226,-1) Go |
In the error log, it logs an entry for the trace flag we enabled.
Now, we took a full SQL database backup and two subsequent transaction log backups in the default backup directory.
|
1 2 3 4 5 |
backup database adventureworks to disk='AdventureWorks.bak' go backup log adventureworks to disk='AdventureWorks_log.trn' go backup log adventureworks to disk='AdventureWorks_log1.trn' |
Backups are successful, as shown below.
Now, if you open the current error log, it does not show any entry for these successful SQL database backups.
Turn on trace flag 3226 using startup parameter
We can enable the trace flag using the startup parameters in SQL Server Configuration Manager. It ensures your trace flags are enabled even you restart SQL services.
Open SQL Server Configuration Manager and in the SQL server properties, navigate to Startup parameters. In this, specify -T3226 trace flag to enable the trace flag in the startup parameter.
Click on Add and Apply. It asks you to restart SQL Services.
Conclusion
In this article, we explored to suppress successful logins and SQL database backup messages in the SQL Server error logs. You should carefully examine your requirements, implement solutions in the lower environment and configure in the production environment with proper testing.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023