SQL Server 2014 Hosting – HostForLIFE.eu :: Learn Control “ERRORLOG” Size & Number of Log Files

Peter Cole | August 30, 2017 | SQL 2014 Hosting | No Comments

SQL Server “ERRORLOG” is a vital tool for DBAs and Developers in order to know varied 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

USE [master]

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'

,N'Software\Microsoft\MSSQLServer\MSSQLServer'

,N'NumErrorLogs'

,REG_DWORD

,99

You can use SSMS to perform the same task, expand the “Management” node in the Object Explorer, and right click the “SQL Server Logs” and select “configure”.

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 2014 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

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

USE [master];

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'

,N'Software\Microsoft\MSSQLServer\MSSQLServer'

,N'ErrorLogSizeInKb'

,REG_DWORD

,10240;

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Related Posts

About The Author

Pete