SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

Limit SQL Server Error Log File Size in SQL Server

SQL Server Error Log contains valuable information which can be used by database administrators to troubleshoot issues with SQL Server. A typical Error Log file contain informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc.

SQL Server Error Log file is initialized every time SQL Server Instance is started. Hence, if SQL Server is not restarted in a while then the error log file will grow very large and it can been extremely difficult to read the information stored within it during critical times. See the follow article to learn How to Recycle SQL Server Error Log file without restarting SQL Server Service.

This article explains how to limit the size of SQL Server Error Log file in SQL Server 2012 and later versions. As a best practice we highly recommend you to backup the registry settings before modifying it. See the follow article to learn How to Increase Number of SQL Server Error Log files in SQL Server.

You can also limit the size of SQL Server Error Log File on SQL Server 2008 R2 apart from SQL Server 2012 and later versions.

Limiting the size of SQL Server Error Log File in SQL Server

Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 10 MB. SQL Server will create a new file once the size of the current log file reaches 10 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
    ,10240;
GO

The below screenshot refers to the registry key prior to the limiting the size of SQL Server Error Log file in Windows Server 2008 R2 for SQL Server 2012.

Registry Setting Before Limiting Size of SQL Server Error Log File Size in SQL Server 2012

In the below screenshot you could see a new entry added with the name ErrorLogSizeInKb along with the value as 10240 KB.

ErrorLogSizeInKB Registry Setting After Limiting Size of SQL Server Error Log File Size in SQL Server 2012

Since SQL Server Error Log hold critical information it is highly recommended to limit the size to a unit which prevents it from growing too large.

Conclusion

This article explains how to limit the size of SQL Server Error Log files in SQL Server 2012 and later versions.

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,500+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips