How to set SQL Server Error Log File size

Arjun JS

6 years ago

In my situation, SQL Server Error log file started overflowing after I enabled the Broker interface to my one of the Database, in this situation to solve log file growing in GBs I searched the web and me got many results in chunks, so by combining all the things solved my problem and same is listed below.

How to Recycle SQL Server Error Log file without restarting SQL Server Service

Recycle SQL Server ErrorLog File using DBCC ERRORLOG Command

USE [master];
GO

DBCC ERRORLOG
GO

Recycle SQL Server Error Log File using SP_CYCLE_ERRORLOG System Stored Procedure

Use [master];
GO

SP_CYCLE_ERRORLOG
GO

Note: For this, you can create an SQL Server Agent Job to recycle SQL Server Error Log once a day or at least once a week.

2. Fixing the size of SQL Server Error Log File in SQL Server 2012 and later versions.

First set no. of log files create

Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 5 MB. SQL Server will create a new file once the size of the current log file reaches 5 MB. This helps in reducing the file from growing enormously large.

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'ErrorLogSizeInKb'
    ,REG_DWORD
    ,5120;
GO

Next in Run type ‘regedit’ to open Registry edit open

Note: Be careful about editing anything here.

navigate to this link : Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer

MSSQL11E.LOCALDB-> this might vary based on your SQL installation.

Here You Have to create a new key that is ErrorLogSizeInKb (If already there then open for modifying)

Also Read: How to Auto Restart SQL SERVER when its crashed?