Managing “ERRORLOG” in SQL Server

In this article we will see and learn how to manage the size and number of log files in SQL Server error log i.e. “ERRORLOG”.

SQL Server Error log:

The most current SQL Server error log is named ERRORLOG and contains information and error messages related to the SQL Server instance and attached databases. Open ERRORLOG in Notepad to review errors.

What Inside an Error Log file of SQL Server?

It comprises information regarding the Audit Collection, Database Mail, Windows Events, SQL Server Agent, SQL Server, Job History, Data Collection. Moreover, it shows a summary report that use to analyze which activities took place in SSMS. So, it can easily troubleshoot by the user.

SQL Server Agent Error log:

The most current SQL Server error log is named SQLAGENT.OUT and contains information and error messages related to the SQL Server Agent and any scheduled maintenance or backups. Open SQLAGENT.OUT in Notepad to review errors.

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) 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”.

SQL Server 2005 to SQL Server 2014: To have 49 (values must be between 6 and 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
 ,49
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”.

2) 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.

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

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

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .