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