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

Published Feb 07 2022 06:39 AM 596 Views
Microsoft

 

Moved from: bobsql.com

 


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

%3CLINGO-SUB%20id%3D%22lingo-sub-3128968%22%20slang%3D%22en-US%22%3ESQL%20Server%20On%20Linux%3A%20Logger.ini%20Size%20and%20Rollover%20Additions%20to%20SQL%20Server%202019%20CU4%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3128968%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CH6%20class%3D%22MsoNormal%22%20id%3D%22toc-hId-1762764347%22%20id%3D%22toc-hId-1762764405%22%3EMoved%20from%3A%20bobsql.com%3C%2FH6%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3EI%20can%E2%80%99t%20take%20credit%20for%20this%20post%20as%20Dylan%20added%20the%20code%20and%20provided%20the%20write-up%20to%20share%20with%20you.%26nbsp%3B%20All%20I%20did%20was%20a%20bit%20of%20editing%20and%20proof%20reading%2C%20enjoy.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EFor%20SQL%20Server%20on%20Linux%2C%20there%20are%20additional%20logging%20capabilities.%20These%20can%20be%20vital%20in%20debugging%20issues%20like%20AD%20authentication.%20The%20internal%20logs%20are%20configured%20using%20%2Fvar%2Fopt%2Fmssql%2Flogger.ini%20text%20file%20and%20can%20be%20configured%20to%20write%20to%20standard%20out%20or%20files.%20For%20example%2C%20the%20logger.ini%20below%20will%20turn%20on%20logging%20for%20Kerberos%20and%20LDAP%20errors%20and%20write%20them%20to%20%2Fvar%2Fopt%2Fmssql%2Flog%2Fsecurity.log.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BOutput%3Asecurity%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EType%20%3D%20File%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EFilename%20%3D%20%2Fvar%2Fopt%2Fmssql%2Flog%2Fsecurity.log%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Silent%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%3Asecurity.kerberos%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Error%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EOutputs%20%3D%20security%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%3Asecurity.ldap%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Error%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EOutputs%20%3D%20security%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3ELogging%20can%20be%20noisy%20and%20consume%20lots%20of%20disk%20space.%20To%20address%20this%2C%20we%20enabled%20the%20ability%20to%20cap%20the%20maximum%20size%20of%20a%20log%20file%20and%20rollover%20log%20files%20when%20they%20reach%20the%20size%20limit.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EStarting%20in%20SQL%20Server%202019%20CU4%2C%20when%20declaring%20a%20file%20output%20(the%20first%20section%20in%20the%20sample%20logger.ini%20above)%2C%20we%20support%20the%20%E2%80%9CMaxfilesizemb%E2%80%9D%20and%20%E2%80%9CMaxrolloverfiles%E2%80%9D%20options.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EMaxfilesizemb%3C%2FSTRONG%3E%20specifies%20the%20maximum%20size%20(in%20MB)%20that%20the%20file%20may%20be%20before%20a%20rollover%20occurs.%20When%20a%20rollover%20is%20done%2C%20we%20follow%20the%20naming%20convention%20of%20SQL%20Server%E2%80%99s%20errorlog.%20For%20example%2C%20security.log%20becomes%20security.log.1%2C%20security.log.1%20becomes%20security.log.2%2C%20and%20so%20on.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThe%20number%20of%20logs%20kept%20is%20defined%20by%20%3CSTRONG%3EMaxrolloverfiles%3C%2FSTRONG%3E.%20After%20we%20reach%20the%20limit%2C%20we%20delete%20the%20oldest%20log%20file.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EHere%20is%20a%20sample%20logger.ini%20using%201GB%20file%20sizes%20and%20keeping%203%20log%20files.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BOutput%3Asecurity%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EType%20%3D%20File%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EFilename%20%3D%20%2Fvar%2Fopt%2Fmssql%2Flog%2Fsecurity.log%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSTRONG%3EMaxfilesizemb%20%3D%201000%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSTRONG%3EMaxrolloverfiles%20%3D%203%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Silent%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%3Asecurity.kerberos%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Error%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EOutputs%20%3D%20security%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%5BLogger%3Asecurity.ldap%5D%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3ELevel%20%3D%20Error%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3EOutputs%20%3D%20security%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin%3A%200in%200in%200in%20.5in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EThe%20security%20logs%20are%20now%20limited%20to%204GB%20of%20disk%20space%20usage%20(1GB%20for%20each%20of%20security.log%2C%20security.log.1%2C%20security.log.2%2C%20and%20security.log.3).%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSTRONG%3EAdditional%20options%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraphCxSpFirst%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3ERemove%20Maxfilesizemb%20or%20set%20it%20to%200%20to%20enable%20unlimited%20file%20size%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraphCxSpMiddle%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3ESet%20Maxrolloverfiles%20to%200%20disable%20rollover%20activity.%20If%20the%20Maxfilesizemb%20setting%20is%20non-zero%2C%20the%20current%20log%20file%20will%20be%20cleared%20whenever%20it%20reaches%20the%20log%20file%20limit%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraphCxSpMiddle%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3EMaxfilesizemb%20is%20capped%20at%204294967295%20(uint32%20max)%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraphCxSpMiddle%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3EMaxrolloverfiles%20is%20capped%20at%20100%20files%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraphCxSpLast%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E-%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3EOn%20boot%2C%20rollovers%20takes%20place%20even%20if%20the%20log%20file%20is%20not%20at%20its%20maximum%20size%20(unless%20Maxrolloverfiles%20is%200)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CSPAN%20style%3D%22color%3A%20%237f7f7f%3B%22%3EDylan%20Gray%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3128968%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20additional%20SQL%20Server%20for%20Linux%20logging.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3128968%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 07 2022 06:38 AM
Updated by: