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

 

The SQL Server Error Log sounds like SQL Transaction Log, which keeps a record of database transactions and ensures database integrity. Right? Unfortunately, the error Log file is not like that.

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!

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 reach to the SQL Server error log location by opening SQL Server Configuration Manager.

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.

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 make this utility special.

• Execute complete analysis of SQL transaction log files without hassles.
• Recover & repair corrupt SQL LDF files as well as 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 error log location at the moment. However, while executing the queries, users need to be very careful. This is because learning how to check error log in SQL Server using query 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-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.