We typically think of error logs as somewhere to go to find issues, but what if your error logs ARE the issue? Like most anything else in SQL Server, if you neglect your error logs you can run into trouble. Even on a low-traffic SQL Server instance, a bad piece of code, or a hardware issue, could easily fill your error logs, and with the introduction of Hekaton in SQL Server 2014, the SQL Server error log started getting a lot more data pumped into it than you might have been used to before. What this means for the DBA is that you can quickly start filling your main system drive (if your SQL install and error logs are in the default location) with massive error logs. So what questions should you be answering about error logs to make sure you don't run into problems?

1. Where are SQL Error Logs stored?
2. How long are error logs kept?
3. How much space do I need for error logs?

Where are SQL Error Logs Stored?

The quickest way to answer this question is to run a simple query:

SELECT SERVERPROPERTY('ErrorLogFileName')

This will return the full path of your current error log. By default this will be located under C:\Program Files\Microsoft SQL Server\.

The location of your logs can be easily changed by adding a startup parameter to the SQL Server service in configuration manager. Simply adding a -e parameter, followed by the desired error log path, will change the path error logs are written to on next service restart. If for example you had a dedicated drive and directory, E:\Logs, that you wanted to write your error logs to, you would add the following startup parameter: -eE:\LOGS.

When adding new startup parameters, it is important to remember that parameters are semi-colon delimited.

How Long are Error Logs Kept?

This question can depend on what, if any, maintenance is being done on your error logs. If you cycle error logs nightly (via a SQL Agent job that executes EXEC sp_cycle_errorlog), you can check the number of SQL error logs that will be kept before re-using the files to determine roughly how many days worth of logs would be kept.

To find the number of error logs that are kept, navigate to the 'SQL Server Logs' node in the object explorer in Management Studio, right click, and select 'Configure'.

From here you can adjust the number of error log files to retain.

If you don't currently do such maintenance it can be hard to say. A new error log is created every time the SQL Server service restarts, or the sp_cycle_errorlog procedure is executed. If your instances don't normally restart there is no real limit to how large the file can get, and therefore, how far back the error log history goes.

If you plan on cycling your logs nightly, and configuring a max number of logs, you should keep in mind that there is a hard limit of 99 files. If you want to keep 90 days worth of logs so you set the number of files to 90, but have 10 restarts within those 90 days, some of your log files will be lost.

How Much Space do I Need for Error Logs?

As stated above, there isn't a practical limit to the size of the error log so the space required for error logs depends on the number of files you keep, how often the error log is cycled, and (if you are using SQL Server 2012+) whether or not a maximum error log file size has been set.

You can check to see if a limit has been set by checking:

For SQL Server 2012:

USE [master];
GO

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb';
GO

For SQL Server 2014+

USE [master];
GO

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
    N'ErrorLogSizeInKb';
GO

This key could change, so you may have to open up the registry editor (regedit) to search for this key if the above commands don't work for you.

Changing this value is just as easy. Instead of executing an xp_instance_regread, we execute xp_instance_regwrite:

USE [master];
GO

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
    N'ErrorLogSizeInKb'
    REG_DWORD,
    1073741824; -- 1GB
GO

These commands all assume you are using the default instance. To try this with different versions of SQL Server and named instances, you would have to replace the MSSQL12 above with the version you are currently running (MSSQL13 for SQL Server 2016), and .MSSQLSERVER with the name of named instance.

Final Thoughts

Error log management can be an important part of managing any SQL Server. Left to their own devices, error logs can become enormous, making it harder for you to open them for troubleshooting purposes, and potentially leading to a full system drive. Cycling the log on a daily or weekly basis, and setting a sane limit on the number of files can go a long way towards keeping these files under control.