Increase or Decrease the number of error log files and their size in SQL Server

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
GO

Similarly, 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.

12 views0 comments