The errorlog in SQL Server stores information about errors and events occuring in a SQL Server instance. These errors include messages like failed login attempts, (un)successful backup messages, Availability Group failover messages, instance configuration information and mini stack dump information in case something went wrong.

Depending on the settings of your instance and some serious problems you have your errorlog may become very big. Which can cause problems if you try to read the errorlog from SSMS. SQL Server by default will only cycle the errorlog after a service restart.

There are a few things you can do to limit the number of records in the errorlog:

• Do not store information about successful backup messages

The purpose of the errorlog is to provide information about things going wrong with your instance. Personally I do not need to read in the errorlog about all the things going right. The number of entries in your errorlog with these kind of messages depend on the number of databases and the number of backups you make. but if you have 10 database on your instance, take 1 full backup and 24 differential backups every day and make log backups every 15 minutes. This will result in 1210 (!) messages per day and it will become very difficult to find the important messages in the error log.

You can suppress these messages by enabling trace flag 3226 on your instance. If you do not know how to enable trace flag you can read it in my post about trace flags found here. Before you enable this trace flag make sure that there are no tools in your environment depending on the successful messages.

• Change the setting for Login auditing

By default this setting is set to Failed logins only and many DBAs don’t change this. While this gives you some degree of auditing it is not the same as true auditing. You can also get Failed logins information from querying the default trace (an example can be found here). If you want a better way to audit login events you may need need to implement server level auditing. If you do not take any action on failed logins why bother logging it in the errorlog? You can change this setting in the Server properties security screen and select None.

Depending on your instance these things can massively reduce the size of your errorlogs. Remember that you also need to cycle your errorlog regularly or set a max file size on them. More information about that and a how-to set this up can be found here (this article mentions SQL Server 2005, but it applies to other versions as well).

If you have any other advice on reducing the events logged in the errorlog, please leave a comment with your advice