How to Open, Read & Check MS SQL Error Log File – Ultimate Tutorial
This blog gives you a big picture of the SQL Server Error Log file, where it is located in the system & the bets way to open & view it. So, let’s have a deeper look at it.
The SQL Server Error Log sounds like SQL Transaction Log, which keeps a record of database transactions and ensures database integrity. Right? Unfortunately, Error Log file is not like that.
Topics To Be Covered
SQL Error Log files are very much different from SQL Server LDF. It majorly comprises User-defined events and is used to troubleshoot system problems. In this technical guide, we are going to provide complete information about Microsoft SQL Server Error Log File.
Let’s get started!
SQL Server Logs & What’s Inside Them?
SQL Server logs are the files storing complete data for the transactions of the SQL Server, whereas error log files are different. It comprises information regarding the Audit Collection, Database Mail, Windows Events, SQL Server Agent, SQL Server, Job History, and Data Collection. Moreover, it shows a summary report that use to analyze which activities took place in SSMS. So, it can easily be troubleshoot by the user.
Where is MS SQL Server Error Log Stored?
You can easily find the SQL Error Log location for your dedicated SQL Server version. All you need to use the following path to access it.
Drive:\ Program Files \ Microsoft SQL Server \ MSSQLV.SQLEXPRESS \ MSSQL \ Log \ ERRORLOG
It is a general path that provides you SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 error log file location. For example, it is the SQL Server 2016 Error Log file location in Windows 10.
C:\ Program Files \ Microsoft SQL Server \ MSSQL13.SQLEXPRESS \ MSSQL \ Log
How to Change Error Log File Location in MS SQL Server 2017/ 2016/ 2014/ 2012
Sometime SQL users need to update the path of an SQL Error log file. The overall procedure is classified into two stages.
Stage 1: Start the SQL Server Configuration Manager
One can open the Configuration Manager with different techniques. All of them are mentioned below.
Method #1 – To open Configuration Manager, you can type the SQL Server Configuration Manager in the search box of Start and open it as shown in the screenshot.
Method #2 – Else, you can use the following path to open SQL Server Configuration Manager.
|SQL Server Version||SQL Server Configuration Manager Exe Location|
|SQL Server 2022||C:\ Windows \ System32 \ SQLServerManager17.msc|
|SQL Server 2019||C:\ Windows \ System32 \ SQLServerManager15.msc|
|SQL Server 2017||C:\ Windows \ System32 \ SQLServerManager14.msc|
|SQL Server 2016||C:\ Windows \ System32 \ SQLServerManager13.msc|
|SQL Server 2014||C:\ Windows \ System32 \ SQLServerManager12.msc|
|SQL Server 2012||C:\ Windows \ System32 \ SQLServerManager11.msc|
|SQL Server 2008||C:\ Windows \ System32 \ SQLServerManager10.msc|
Here, we opened the SQL Server Configuration Manager with the above–mentioned location in SQL Server 2017.
Method #3 – You can also open the Run dialog box and type ‘compmgmt.msc’ in the box.
From the following screen, expand the Services and Applications section and get the SQL Server Configuration Manager.
Stage 2: Change the SQL Server Error Log Path
Step-1. Under the SQL Server Configuration Manager, go to the SQL Server Services option.
Step-2. Perform right-click on SQL Server(SQLEXPRESS) as shown in the screenshot and choose the Properties option.
Step-3. From the SQL Server(SQLEXPRESS) Properties, click on the Startup Parameters tab. Afterward, under the Existing parameters section, choose the log file whose location you want to change.
Step-4. The starting prefix (like e,d) and the current Error Log file Name will not change. Rest of the folder can be changed. Mention the new path under the Specify a startup parameter box and update it.
Step-5. The changes to SQL error logs location are saved but they are not active. For this. You need to start the SQL Server Services.
Quick Steps to Open SQL Server Error Logs
- Launch Log Analyzer & Add SQL Server Error Log.
- Set offline or Online Mode & Enter Server Details.
- Preview the Concerned Files and then Proceed.
- Set the Filters and the Destination Location.
- Click on Export to Get the Desired Results.
Methods to Open SQL Server Error Log Files
There are two ways through which one can view the Error Log. Now, whenever, the SQL Server could not open Error Log file, then use any of the below-mentioned techniques. Also, users need to know that these methods are not advised to how to check error log in SQL Server database.
Approach #1: Use T-SQL to Read SQL Error Log
Connect with the SSMS and type ‘xp_readererrorlog’ in the panel. It is a SQL Server query to read Error Log.
Approach #2: Use Object Explorer Option
Without the help of T-SQL, you can also view the error log. Finally, let’s check out how to read SQL Server Error Log.
Step-1. Open Microsoft SQL Server Management Studio on your system.
Step-2. Time to connect with Object Explorer. Open Connect to Server Window and select the Server name. Afterward, choose the Authentication Type and click on Connect.
Step-3. Go to the Management section from the Object Explorer in order to read SQL Error Log.
Step-4. Expand the Management Section and go to the SQL Server Logs.
Step-5. Right-click on SQL Server Logs option. Go to the View option and select SQL Server Log from the menu to view errorlog SQL Server database.
Step-6. The Log File Summary will appear on the screen. From the same window, you can opt for different Log – SQL Server Agent, Database Mail.
That’s all about Microsoft SQL Server Error Log.
Automated Solution to Read, Examine & Learn How to Check Error Log in SQL Server
Well, the manual method works, but not all the time. The success ratio here is quite low. Evidently. experts recommend users opt for an advanced automated solution. Evidently, this is exactly why experts recommend the automated solution.
Moreover, if you want to examine the SQL Transaction Log file, then you can try SysTools LDF File Reader Software. Download this tool & then simply follow the below instructions to read & examine your SQL error log files.
Step-1. Launch the Software & then Click on the Open button to add LDF log files.
Step-2. Select the Online or Offline mode & Enter SQL Server Details in the dialog box.
Step-3. Preview the Files that you are concerned with by selecting them from the left panel.
Step-4. Apply Filters if you want to Export the records for further examination.
Step-5. Click on the Export button to get the records on your preferred location.
Automated Tool Features for SQL Server Logs
The automated tool is way better than the manual one because of several factors. Let’s have a look at these features in detail that makes this utility special.
- Execute complete analysis of SQL transaction log files without hassles.
- Recover & repair corrupt SQL LDF files as well to restore the database files.
- Analyze all transactions like Insert, Update, and Delete with advanced features.
- Easily examine the files without any need for the SQL Server application.
- Supports data types: Datetime2, datetimeoffset, sql_variant, hierarchyid, geometry & geography.
- Microsoft SQL Server 2019, 2017, 2016, 2014, 2012, 2008 & SQL Server 2005 LDF are supported here.
How to Check Error Log in SQL Server with T-SQL Method Manually?
We can also use the SERVERPROPERTY() command to get the location of an error log file in SQL Server at the moment. However, while executing the queries, users need to be very careful. This is because these commands can be risky & might result in data loss.
SELECT SERVERPROPERTY('Name_of_Error_Log_File') AS 'Error log file location';
After executing the queries, users can browse in the directory to find the records. They can see the error logs there without any hassles.
- ErrorLog: Error log file at the moment.
- ErrorLog.1: Error log file 1 of the archive
- ErrorLog.2: Error log file 2 of the archive
Drawbacks of the Manual Method to View SQL Server Logs
Now, these manual solutions are full of shortcomings that can be highly dangerous for users’ log files. We are aware of how crucial a Log data file in SQL Server is. How it helps in tracking all the transactions & the user who executed that is what we learn from it.
Let’s quickly have a look at the manual method drawbacks in depth:
Very Poor Interface: The interface of the manual solutions is very poor. The T-SQL commands are not interactive at all & spoils user experience. Moreover, when we talk about the SSMS Method, it’s not also that good.
No Technical Expertise: These manual solutions require more technical proficiency than just the basic knowledge. This makes this task more complex & hard to execute. Also, it’s true that users might be needing SQL experts’ assistance for such methods.
Lack of Customization Features: For every single task, there is a long command. There are no features for getting the customised results just like what users need. Moreover, users must understand that they can’t get the exact results as expected everytime.
Security Standards: There is no protective layering if users are opting for such manual methods. However, the automated solution keeps the original file intact to avoid any corruption or data integrity issues.
Interrupts Business Hours: To find the errorlog SQL Server with manual solutions, users need to be prepared for long time consuming tasks. This can reduce efficiency & even interrupt the usual working hours of the business.
Finally, after understanding all the core concepts of SQL Server logs carefully, it’s time for us to conclude this article. Also, we know the best ways as well to open, read & check SQL Server error logs in a smart manner. The above-mentioned manual & automated solutions are good enough for users to get the desired results they want.
However, when we dig deep, it’s very easy for us to say that manual solutions are a bit complex. Therefore, as per experts’ opinion, the automated tool is the only reliable option left which makes the task hassle-free.
Frequently Asked Questions –
Ans: The location of Error Log – C:\ Program Files \ Microsoft SQL Server \ MSSQLn.SQLEXPRESS \ MSSQL \ Log, where n is equivalent to the number basis on the Server version.
Ans: Open ErrorLog using Log File Viewer in Management Studio and analyze all the events happened in the system.
Ans: The SQL Log or Transaction Log file keeps record of every transaction performed on the database. Whereas, the Error Log used to keep track of events happened in SSMS related to ports, network and much more.
Q-4. What’s the best way to find error logs but not their path?
Ans: Firstly, check for the error message. Now, examine errlog.log primarily. Also, check the optional log files for errors. Identify the respective errors easily.
Q-5. How to create an error log in SQL Server?
Ans: Follow the instruction as mentioned carefully:
- Go to Object Explorer & Expand Database.
- Navigate to Expand Management.
- Right Click SQL Server Logs here.
- Now Click on Configure to create errors.