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
Recycle SQL Server Error Log File using SP_CYCLE_ERRORLOG System Stored Procedure
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.
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)
In Modify select Decimal and put ‘1’ in the textbox and save.
That’s it Now its good to go… 🙂
Arjun is is a web developer/designer, who is fond of the web. He lives in Manipal, Karnataka, India.