Rajendra Gupta
Take DB backups

Suppress successful login and backups messages from the error log for SQL databases

September 28, 2020 by

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.

Login success messages for SQL database

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.

Login Auditing

In the below image, we see both successful and failed logins.

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.

Disable successful login messages

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.

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.

Backup success messages

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.

In the error log, it logs an entry for the trace flag we enabled.

Turn on trace flag 3226

Now, we took a full SQL database backup and two subsequent transaction log backups in the default backup directory.

Backups are successful, as shown below.

Take DB backups

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.

Turn on trace flag 3226 using startup parameter

Click on Add and Apply. It asks you to restart SQL Services.

Restart SQL Service

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.

Rajendra Gupta
166 Views