The SQL Server Error Log file is per SQL Server instance. A DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server easily.
Finding location of Windows Error logs
DBA can identify SQL Server Error Log file used by SQL Server Database Engine Using Application Event Viewer
- Click Start -> All Programs -> Administrative Tools -> Server Manager.
Finding location of SQL Server error log in Windows Error logs
- In Server Manager-> expand Diagnostics-> expand Event Viewer-> 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.
- The location of SQL Server Error Log file is in the event properties.
Finding location of SQL Server error log using SQL Query
- Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending
By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.
Alternate way finding location of SQL Server error log
- DBA can also identify SQL Server Error Log File used by SQL Server Database Engine Using SQL Server Configuration Manager
1. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager
2. 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. For a named instance, right click on the appropriate SQL Server (INSTANCE-NAME) service.
3. In SQL Server (MSSQLSERVER) Properties window click on the Advanced tab and then expand the drop down next to Startup Parameters. The location of SQL Server Error Log file is mentioned next to the “-e” startup parameter.