Controlling the “ERRORLOG” – size and number of log files

SQL Server “ERRORLOG” is a vital tool for DBAs and Developers in order to understand various events that are logged in it. Thus, maintaining its growth and keeping the number of log files is important.

1.0: Number of “ERRORLOG”.

We can keep up to 99 “ERRORLOG” files while 6 are default. To increase the number of “ERRORLOG”s, we can use the SSMS directly or we can use the extended Stored Procedure “xp_instance_regwrite”.

1.0.1: SQL Server 2005 to SQL Server 2014: To have 99 “ERRORLOG”s, execute the following query:

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'

    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'

    ,N'NumErrorLogs'

    ,REG_DWORD

    ,99

GO

To perform the same task using SSMS, expand the “Management” node in the Object Explorer, and right click the “SQL Server Logs” and select “configure”.

Figure #1: Number of log setting in SSMS

2.0: Size of “ERRORLOG”:

SQL Server 2005 to 2008 Errorlog size can only be managed manually. While SQL 2012 onwards, a mechanism has been built within the tool to control the “ERRORLOG” size automatically.

2:0.1: SQL Server 2005 to SQL Server 2008: The following query can be used to determine the size of the current “ERRORLOG”. Based on this size, the “ERRORLOG” then can be recycled. A scheduled SQL Agent job can do this trick:

SET NOCOUNT ON

CREATE TABLE #Errorlog

(

       ArchiveNo           INT

   ,ArchiveDate         DATETIME

   ,LogFileSizeBtye     BIGINT

);

      

INSERT INTO #Errorlog

EXEC xp_enumerrorlogs

IF (

       SELECT dt.LogFileSizeMB

       FROM   (

                  SELECT e.ArchiveNo

                        ,e.ArchiveDate

                        ,(e.LogFileSizeBtye/1024) AS LogFileSizeKB

                        ,(e.LogFileSizeBtye/1024)/1024 AS LogFileSizeMB

                  FROM   #Errorlog e

                  WHERE  e.ArchiveNo = 0

              ) dt

   )>=10 -- if errorlog is more than 10mb

BEGIN

    PRINT 'Recycling the error log'

    DBCC ErrorLog -- recycle the errorlog

END

      

DROP TABLE #Errorlog

2.0.2: SQL Server 2005 to SQL Server 2014: To control the “ERRORLOG” size starting from SQL Server 2014, we can execute the following query to set the desired log size in KB.  In the following example we have set the log size as 10MB (10240 KB).

USE [master];

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'

    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'

    ,N'ErrorLogSizeInKb'

    ,REG_DWORD

    ,10240;

GO