How to manage the SQL Server error log

 

Reinitializing SQL Server error logs

PS C:\Users\Administrator> sqlcmd -S "10.0.2.15\PROD,3433"
1> xp_enumerrorlogs
2> go
Archive #   Date                     Log File Size (Byte)
----------- --------                --------------------
          0 09/22/2014  21:26         0
          1 09/22/2014  21:26           19192
          2 09/17/2014  15:57         18486
          3 09/02/2014  21:28         29756
          4 09/02/2014  20:36         19254
          5 08/28/2014  15:25         18366
          6 08/27/2014  16:38         50636
(7 rows affected)

1> sp_cycle_errorlog
2> go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Limiting the size of SQL Server error logs (2012 and later version)

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb'
2> go

(0 rows affected)
1> EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb', REG_DWORD, 5120;
2> go

(0 rows affected)
1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb'
2> go
Value                 Data
---------------     -----------
ErrorLogSizeInKb     5120
(1 rows affected)

 

Increasing the number of SQL Server error log

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
2> go
(0 rows affected)

1> EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12
2> go
(0 rows affected)

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
2> go
2> go
Value                 Data
---------------     -----------
NumErrorLogs         12
(1 rows affected)