Updated: Dec 31, 2021
SQL Server error logs are essential for troubleshooting issues, checking out any warning or errors raised by various SQL Server processes, gathering instance information such as startup time, certificate, database recovery progress, backups, and many more.
Therefore, you should know the directory that holds these SQL Server logs.
The latest error log file can be found using the XP_READERRORLOG extended stored procedure.
USE master GO xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL GO
OR Use SERVERPROPERTY() function to return the error log directory.
SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log location'
One more way to find error log details without logging to SQL Server instance is to check SQL Server Configuration Manager. RDP to SQL Server, launch SQL Server Configuration Manager from Start Menu and check startup parameters. The error log location is specified using the -e switch.
You can refer to article - How to manage SQL Server logs effectively for managing SQL Server logs effectively.