The newly installed SQL Server instance or default configured instance uses a current and 6 archived error log files. However, it is advisable to configure the log files between 6 to 99 for investigating any issues.
Note: The minimum configured value for error logs is 6 (default value)
The following SQL Script configures the default number of error log files to 20.
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GOSimilarly, SQL Server does not restrict the size of the error log. It can store unlimited content. However, you might face timeout issues while checking errors from a large error log. The following script changes the size of the current error log file to 512 KB( 512*1024= 524,288 KB).
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 524,288
GO
Looking to manage SQL Server log effectively? If yes, refer to article - https://www.sqlshack.com/how-to-manage-sql-server-logs-effectively/
for more details.
Comments