Configure/Manage Error logs – SQL Server on Linux.

How to configure and manage SQL Server Error logs in Linux environment:

In this blog post let’s see how to configure and manage SQL Server error logs on an Instance running in Linux environment. I am sure most of you are already recycling (re-init) your error logs (exec sp_cycle_errorlog) on regular basis by scheduling a job and controlling the number of logs as needed on SQL@Windows. Well, If you are not doing so, please go ahead and do yourself a favor…configure your error logs. By default on SQL@windows, it keeps 6 archives (128 in SQL@Linux). Imagine where your SQL got restarted multiple times(let’s say 6 times) in short duration for whatever reason, Oops! your precious logs are already gone. See below screenshots on how to do that for SQL@windows,
Using object explorer in SSMS:

Using T-SQL:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Now let’s move on to SQL@Linux. If you notice the above T-SQL code, SSMS is basically updating windows registry values behind the scenes which isn’t possible in Linux. (Registry is an exclusive windows OS thing…Duh…..!) See below screenshot, you don’t even get configure option in SSMS under SQL Server logs (BTW, Did you notice the tiny Penguin!).

On my lab server with default settings, You can notice that I have 82 error logs as of now.(By Def, max 128 in SQL@Linux and 6 in SQL@Windows)!

Let’s say I want to set it to 20,  what I have to do is, use mssql-conf utility and specify the desired number.

sudo /opt/mssql/bin/mssql-conf set errorlog.numerrorlogs 20

The changes will take effect after the service restart and you can validate by reading the contents of mssql.conf file as shown below.

Note: Old error log files should be manually removed or moved as needed.

Hope this helps, Happy Monday!