All about SQL Server Error logs

SQL Server error logs are one of the important stuff to check whenever DBA face issues in the SQL Server instance. It contains lot of information about the Issues, Errors, Backup related information and Database consistency check occurrences based on the Trace Flag configuration of SQL Server instance. SQL Server error log file is recycled whenever SQL Service is restarted or we can also recycle the error log file manually in case error log file size grows drastically. SQL Server keeps 6 archived copies of the error log files by default and one latest Errorlog file, so total we have 7 SQL Server error log files in the Log folder.

How To check SQL Server Error Log:

Many a times when DBAs are new to the environment and they don’t know about the Installation standards of SQL Server instance in their Organization, they struggle in finding the Error log file location to troubleshoot SQL Server issues. Following are the different ways through which you can find the Error logs without any issues.

1) Through SQL Server Management studio using xp-readerrorlog

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
GO

2) Through SQL Server Management studio using xp_readerrorlog

use master
go
sp_readerrorlog 0, 1, 'Logging SQL Server messages in file'
go

3)  SQL Server Configuration Manager:

Apart from this we can also check the SQL Server Error log location from SQL Server Configuration Manager. We can find the error log file location under –e parameter as displayed below.

Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager

Right Click on the SQL Services and click on Properties –> Under Advance tab -> Click on Startup Parameters

4) Windows Event Viewer:

We can also check the Windows event viewer details to find out SQL Server log file location as per the following screen shot.

 Event Viewer -> Windows Logs -> Application
Filter the error logs using Event ID ->17111.  Click OK.

We will get the events showing the SQL Server Error log file path in the details.
This is how we can check SQL Server error logs for any kind errors or information to troubleshoot issues. If there is any requirement to log particular events from SQL Server instance, we can enable specific Trace Flags as well (will cover this topic in another blog), through which we can get more in depth information about particular issue or configuration.

Whenever SQL Server instance is restarted, a new SQL Server error log file is created. However, if required we can manually recycle the error log using below command.

1. DBCC ERRORLOGS
2. sp_cycle_errorlog

SQL Server Error Log files retention:

As mentioned earlier SQL Server retains 6 Archived and 1 Active error log files, it is a best practice to increase this number to at least 50 so that If SQL Server Instance is started multiple times, we do not lose the older log file which can be used to perform root cause analysis for the occurred issue.

How to increase the number of SQL Server Error log files retention?

1. Connect to the SQL Server instance -> Click on Management -> SQL Server Logs Right click on “SQL Server Logs” and then click on “Configure”

2. Click on “Limit the number of error log files before they are recycled” and click “OK”. Change the number of files as per the requirement and click OK.

That’s all about SQL Server error log in SQL Server instance location and its retention. I also covered how we can recycle the SQL Server error logs in case of any requirement. SQL Server error logs are an important tool for troubleshooting and monitoring the performance of a SQL Server instance. Understanding the location of these logs, how to configure their retention, and how to recycle them when necessary, is an essential part of managing and maintaining a SQL Server instance. By regularly reviewing and monitoring the error logs, you can proactively identify and address any issues that may arise, ensuring that your SQL Server environment is operating at optimal performance.