SQL CIS hardening doc for SQL server

%3CLINGO-SUB%20id%3D%22lingo-sub-1194959%22%20slang%3D%22en-US%22%3ESQL%20CIS%20hardening%20doc%20for%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1194959%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3EAs%20per%20the%20point%205.1%20in%20CIS%20hardening%20doc%20for%20SQL%20server%20(Ensure%20'Maximum%20number%20of%20error%20log%20files'%20is%20set%20to%20greater%20than%20or%20equal%20to%20'12'%20(Scored))%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20increased%20the%20number%20of%20error%20log%20files%2C%20but%20i%20need%20guidance%20on%20what%20should%20i%20keep%20as%20their%20maximum%20size%20(in%20KBs)%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESQL%20Version%20%3A%202017%20Enterprise%20edition%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22arunakiri_0-1582690821632.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173553i986D7BFCCECC08BF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22arunakiri_0-1582690821632.png%22%20alt%3D%22arunakiri_0-1582690821632.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1228712%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20CIS%20hardening%20doc%20for%20SQL%20server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1228712%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568219%22%20target%3D%22_blank%22%3E%40arunakiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20first%20glance%20it%20sound%20like%20a%20question%20that%20related%20to%20third%20party%20company%20CIS%20and%20it%20is%20not%20related%20to%20this%20forum%20site.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20SQL%20Server%20there%20is%20no%20specific%26nbsp%3Bmaximum%20size%20you%20should%20use%20for%20the%20error%20log%20files%20and%20each%20case%20should%20be%20examine%20according%20to%20your%20system.%26nbsp%3BSQL%20Server%20Error%20Log%20file%20are%20initialized%20every%20time%20SQL%20Server%20Instance%20is%20started%20or%20you%20execute%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-stored-procedures%2Fsp-cycle-errorlog-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3Esp_cycle_errorlog%3C%2FSPAN%3E%3C%2FA%3E.%20If%20you%20have%2012%20files%20then%20each%20time%20the%20log%20file%20is%20initialized%20a%20new%20file%20is%20created%20and%20old%20one%20removed.%20Hence%2C%20if%20SQL%20Server%20is%20not%20restarted%20and%20you%20do%20not%20execute%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-stored-procedures%2Fsp-cycle-errorlog-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3Esp_cycle_errorlog%3C%2FSPAN%3E%3C%2FA%3E%20in%20a%20while%20then%20the%20error%20log%20file%20might%20grow%20very%20large.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBack%20to%20your%20question%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3Ei%20need%20guidance%20on%20what%20should%20i%20keep%20as%20their%20maximum%20size%20(in%20KBs)%20%3F%3C%2FP%3E%0A%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%0A%3CP%3E%3CSPAN%3ELike%20most%20questions%20the%20answer%20is%3A%20it%20depends.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EIt%20depends%20on%20your%20system.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20most%20cases%20it%20is%20not%20common%20to%20limit%20the%20file%20size%20but%20the%20file%20time.%20For%20example%20it%20is%20more%20common%20to%20start%20new%20file%20each%20day%20(using%20a%20job%20for%20example%20that%20execute%26nbsp%3Bsp_cycle_errorlog).%20If%20you%20limit%20by%20file's%20size%20then%20it%20might%20be%20harder%20to%20follow%20the%20content%20since%20you%20do%20not%20know%20where%20look%20for%20it%2C%20but%20if%20you%20have%20a%20separate%26nbsp%3Bfile%20each%20day%20or%20each%20week%20or%20each%20month%20(according%20to%20the%20amount%20of%20the%20is%20logged%20and%20the%20history%20you%20want%20to%20keep)%20then%20you%20have%20simple%20way%20to%20know%20where%20the%20data%20is%20stored%20by%20that%20period%26nbsp%3Bof%20time%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20do%20want%20to%20limit%20the%20size%20then%20you%20can%20use%20the%20following%20code%26nbsp%3BIn%20old%20version%20of%20SQL%20Server%20from%20SQL%20Server%202012%20and%20later%20versions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EUSE%20%5Bmaster%5D%3B%0AGO%0AEXEC%20xp_instance_regwrite%20N'HKEY_LOCAL_MACHINE'%2C%0A%20%20%20%20N'Software%5CMicrosoft%5CMSSQLServer%5CMSSQLServer'%2C%0A%20%20%20%20N'ErrorLogSizeInKb'%2C%20REG_DWORD%2C%205120%3B%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20new%20versions%20you%20can%20control%20the%20size%20directly%20in%20the%20SSMS%20Object%20explorer%3A%20Select%20Management%20-%26gt%3B%20Ritgh%20click%20on%20SQL%20Server%20Logs%20-%26gt%3B%20select%20Configure%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorR%20A_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22f.png%22%20style%3D%22width%3A%20687px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176976i178841FA50D65E68%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22f.png%22%20alt%3D%22f.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAgain%2C%20you%20will%20need%20to%20choose%20the%20size%20according%20to%20your%20system.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi ,

As per the point 5.1 in CIS hardening doc for SQL server (Ensure 'Maximum number of error log files' is set to greater than or equal to '12' (Scored)) ,

 

We have increased the number of error log files, but i need guidance on what should i keep as their maximum size (in KBs) ?

 

SQL Version : 2017 Enterprise edition

 

 

arunakiri_0-1582690821632.png

 

 

1 Reply

Good day @arunakiri 

 

In first glance it sound like a question that related to third party company CIS and it is not related to this forum site.

 

From SQL Server there is no specific maximum size you should use for the error log files and each case should be examine according to your system. SQL Server Error Log file are initialized every time SQL Server Instance is started or you execute sp_cycle_errorlog. If you have 12 files then each time the log file is initialized a new file is created and old one removed. Hence, if SQL Server is not restarted and you do not execute sp_cycle_errorlog in a while then the error log file might grow very large.

 

Back to your question:

 

i need guidance on what should i keep as their maximum size (in KBs) ?


Like most questions the answer is: it depends. It depends on your system.

 

In most cases it is not common to limit the file size but the file time. For example it is more common to start new file each day (using a job for example that execute sp_cycle_errorlog). If you limit by file's size then it might be harder to follow the content since you do not know where look for it, but if you have a separate file each day or each week or each month (according to the amount of the is logged and the history you want to keep) then you have simple way to know where the data is stored by that period of time

 

If you do want to limit the size then you can use the following code In old version of SQL Server from SQL Server 2012 and later versions:

 

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

 

 

On new versions you can control the size directly in the SSMS Object explorer: Select Management -> Ritgh click on SQL Server Logs -> select Configure

 

f.png

 

Again, you will need to choose the size according to your system.