How to Open, Read & Check MS SQL Error Log File – Ultimate Tutorial

  author
Written By Ashwani Tiwari
Anuraag Singh
Approved By Anuraag Singh
Published On September 19th, 2023
Reading Time 10 Minutes Reading

 

sql server logs

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

error log location

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.

change error log location in SQL Server

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.

configuration

Method #3 – You can also open the Run dialog box and type ‘compmgmt.msc’ in the box.

command

From the following screen, expand the Services and Applications section and get the SQL Server Configuration Manager.

expand services

Stage 2: Change the SQL Server Error Log Path

Step-1. Under the SQL Server Configuration Manager, go to the SQL Server Services option.

services

Step-2. Perform right-click on SQL Server(SQLEXPRESS) as shown in the screenshot and choose the Properties option.

properties

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.

set parameters of SQL server error log

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.

xp_readererrorlog

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.

connect to server

Step-3. Go to the Management section from the Object Explorer in order to read SQL Error Log.

Go to Management

Step-4. Expand the Management Section and go to the SQL Server Logs.

Go to 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.

Click View

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.

Open SQL Server Error Log

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.

Download Now Purchase Now

Step-1. Launch the Software & then Click on the Open button to add LDF log files.

add LDF files

Step-2. Select the Online or Offline mode & Enter SQL Server Details in the dialog box.

enter server credentials

Step-3. Preview the Files that you are concerned with by selecting them from the left panel.

read error log files

Step-4. Apply Filters if you want to Export the records for further examination.

enter filters for export

Step-5. Click on the Export button to get the records on your preferred location.

export sql server error log file

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.

Conclusion

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 –

Q-1. How to find log path where the files are kept?

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.

Q-2. How to perform SQL Server error log monitoring?

Ans: Open ErrorLog using Log File Viewer in Management Studio and analyze all the events happened in the system.

Q-3. What is the difference between SQL Log & SQL ErrorLog File?

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.