We are going to discuss 5 methods to find location of SQL Server Error Log File. We will expose different ways for a DBA to identify SQL Server Error log location. The Error log file named “ERRORLOG” can be valuable to track some issue for SQL Server. Contains user-defined events and certain system events you can use for troubleshooting.
In this tip we will take a look at 5 different ways you identify which SQL Server Error Log file is used by an instance of SQL Server.
- Using tsql command (SERVERPROPERTY)
- Run xp_readerrorlog.
- Using SQL Server Configuration Manager
- Application Event Viewer
- Search on registry key
5 ways to find SQL Server Error Log File – ERRORLOG
Identify Sql Server Error Log file using tsql command
Execute SERVERPROPERTY function as below:
AS ‘Error log file location’
You will get the results:
By default, we have 6 Server Error Logs kept.
EXEC xp_readerrorlog 0, 1, N’Logging SQL Server messages in file’
Also you can run xp_readerrorlog and find the line says “Logging SQL Server messages”.
Finding Sql Server Error Log file using SQL Server Configuration Manager
- Search on Windows for – SQL Server Configuration Manager
- In SQL Server Configuration Manager, click SQL Server Services on the left side and then right-click on SQL Server (MSSQLSEVER)and select Properties from the drop down as shown below. For a named instance, right-click on the appropriate SQL Server (INSTANCENAME)
- In SQL Server (MSSQLSERVER)Properties window click on the Startup Parameters. We need to find startup parameter starting with -e.
Locate Sql Server Log file location Using Application Event Viewer
- On windows search for “Event Viewer”
- In Server Manager, expand Diagnostics, expand Event Viewer, expand Windows Logsand then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111 as shown in the below snippet. To set a filter right-click on Application and select Filter Current Log.
- Expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111.
- Double click an event and you can see the event properties as shown below.
Find location of SQL Server Error Log File Searching on registry key
The ERRORLOG is one of startup parameters and its values are stored in registry key that you can find like below:
- Go to regedit by searching on windows
- Go to path – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server and expand your sql server version
- SQLArg1 shows parameter starting with -e parameters which point to Errorlog file.