SQL Server On Linux: Logger.ini Size and Rollover Additions to SQL Server 2019 CU4

SQL Server On Linux: Logger.ini Size and Rollover Additions​​ to SQL Server 2019 CU4


I can’t take credit for this post as Dylan added the code and provided the write-up to share with you. ​​ All I did was a bit of editing and proof reading, enjoy.

For SQL Server on Linux, there are​​ additional logging capabilities. These can be vital in debugging issues like AD authentication.​​ The internal​​ logs are configured​​ using​​ /var/opt/mssql/logger.ini​​ text file​​ and can be configured to write to standard out or files. For example, the logger.ini below will turn on logging for Kerberos and LDAP​​ errors​​ and write them to /var/opt/mssql/log/security.log.

[Output:security]

Type = File

Filename = /var/opt/mssql/log/security.log

 

[Logger]

Level = Silent

 

[Logger:security.kerberos]

Level = Error

Outputs = security

 

[Logger:security.ldap]

Level = Error

Outputs = security


Logging​​ can be​​ noisy and​​ consume lots of​​ disk​​ space. To​​ address​​ this, we enabled​​ the ability to​​ cap the maximum size of a log file and rollover log files when they​​ reach​​ the size limit.

Starting in SQL Server 2019 CU4, when declaring a file output (the first section in the sample logger.ini above), we support the “Maxfilesizemb” and “Maxrolloverfiles” options.​​ 

Maxfilesizemb​​ specifies​​ the maximum size (in MB) that the file may be before a rollover occurs. When a rollover is done, we follow the​​ naming convention​​ of​​ SQL Server’s​​ errorlog. For example, security.log becomes security.log.1, security.log.1 becomes security.log.2, and so on.​​ 

The number of logs​​ kept​​ is defined by​​ Maxrolloverfiles. After we​​ reach​​ the​​ limit, we delete the oldest log file.

Here is a​​ sample logger.ini​​ using​​ 1GB​​ file sizes​​ and keeping​​ 3 log files.

[Output:security]

Type = File

Filename = /var/opt/mssql/log/security.log

Maxfilesizemb = 1000

Maxrolloverfiles = 3

 

[Logger]

Level = Silent

 

[Logger:security.kerberos]

Level = Error

Outputs = security

 

[Logger:security.ldap]

Level = Error

Outputs = security

 

The security logs​​ are now​​ limited to 4GB of​​ disk​​ space​​ usage​​ (1GB for each of security.log, security.log.1, security.log.2, and security.log.3).

Additional options:

  • Remove​​ Maxfilesizemb or set it to 0​​ to enable unlimited file size

  • Set Maxrolloverfiles to 0​​ disable rollover activity. If​​ the​​ Maxfilesizemb​​ setting​​ is non-zero, the current log file will be cleared whenever it​​ reaches​​ the log file limit

  • Maxfilesizemb is capped at 4294967295 (uint32 max)

  • Maxrolloverfiles is capped at 100 files

  • On boot, rollovers​​ takes place​​ even if the log file is not at its maximum size​​ (unless​​ Maxrolloverfiles is 0)

Dylan Gray
Bob Dorr