Identifying the Error Log File Location in SQL Server
Transaction log files contains records of all database transactions to help users in ensuring data integrity at the time of system restore. However, there are many other log files for diagnosing and troubleshooting problems. In this blog,we will be discussing about one such log file i.e. SQL Server Error Log File and find various ways to identify the location of this Error Log File file.
What is SQL Server Error Log File?
The Error Log File associated with SQL Server Database is one of the most important log files, which is used to troubleshoot system problems. The SQL Server retains backups of previous six logs, with the names of each files given sequentially. The default name of the error log file is ERRORLOG. The SQL server error log file contains not only the errors, but also the information related to the error.
How to identify SQL Server Error Log File location?
Some of the ways to identify the error log file location in SQL Server are as follows:
- Using T-SQL Command
The first option will work when the SQL Server is running and is connected to the desired database. Use below T-SQL command that uses extended stored procedure XP_READERRORLOG.
Parameters used in the command are as follows-
xp_readerrorlog >> Extended Stored Procedure
0 >> Value of error log file i.e. 0=current; 1=Archive1; 2=Archive2 etc.
1 >> Type of Log File i.e. 1 or NULL=Error Log; 2=SQL Agent Log
Logging SQL Server messages in file >> Search string entered by the user
This will help in reading the SQL Server Error Log to find location of errorlogs used by the SQL Server Instance.
- Using SQL Server Configuration Manager
We can find the location of error log file using SQL Server Configuration Manager. This option is used when the SQL Server is running but user is not able to connect. The steps for using SQL Server Configuration Manager are as follows:
Using Windows Application Event Viewer
- Go to Start > All Programs > Microsoft SQL Server (version) > Configuration Tools > SQL Server Configuration Manager
- A window of SQL Server Configuration Manager will be opened. Select SQL Server Services and go to SQL Server, right-click on it and select Properties
- In the SQL Server Properties window, click on Advanced Tab and go to Startup Parameters. The SQL Server error log file location will be given after ‘-e’ in the next column of the Startup Parameters.
The last option for finding Error log file location in SQL Server is by using Application Event Viewer and we will follow the steps below:
- Go to Start > All Programs > Administrative Tools > Server Manager
- In the Server Manager window, Expand Diagnostics > Event Viewer > Windows Logs > Application
- Open the events log and select the desired event ID. Right-click on Application and select Filter Current Log
- Double-click on event to view the location of SQL Server Error Log File using Event Properties Windows
The blog discusses the importance of log files used in SQL Server Database. Users are aware of only transaction log files used for recording every transaction. However, many other log files are used such as Error Log Files that describes not just the errors but also the information related to that error. As the Error log file plays an important role to troubleshoot the system issues, the blog discusses several possible ways on how to check SQL Server Error logs.