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

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:

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:

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

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.