Ingenious Guide to View Log File of SQL Server

Microsoft SQL Server application is one of the biggest waves in the relational database management system and handles huge databases in a well-structured manner. However, when it comes to view log file of SQL Server, some users face trouble. We are sure that you must be aware of how good the MS SQL Server database is for both beginner as well as expert users. However, here, we’re gonna discuss about MSSQL viewer for in-depth solutions to let users read the log file of SQL Server without errors.

Digital Crimes Can Take Place In SQL Server Too! 

Nowadays, it has become quite common in organizations that ex-employees or users with malicious intent modify the data in the databases in order to attack and damage the organization’s assets. However, it is much more complex to find out the activities and the users who might have manipulated the data, and further to find the real culprit. Now, when an organization’s data is compromised, it leads to various concerns like data privacy, security, and further trust issues of the users and employees, demanding that the organization find the best ways to view log file of SQL Server. As a result of these malicious activities in the database, the organizations end up facing various challenges with respect to their safety and users’ trust. Now, the major concern among users and organizations is how the activities done in the database can be tracked and how the real culprit can be found. 

SQL Server Read Transaction Log to Find the Real Culprit

In the SQL Server environment, there is a Transaction Log file that stores records of all the transactions & modifications done in a database. By reading this Transaction Log file, one can easily check who deleted data from table in SQL Server database. Additionally, the transaction logs are also helpful for forensic investigators for examining the SQL Server transactions. Furthermore, these log files allow the database administrators to view & check every transaction in a detailed manner. Therefore, with the help of these SQL Server Log files, it becomes much easier and convenient to find answers to questions like which query was performed on which table at what time and by whom.

Now, we will answer the most common query of the users, that is, how to view log file of SQL Server? We will proceed to SQL Server read transaction log by using various workarounds. Just go through this article once and understand how to open or read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.

Moreover, if users want to restore the deleted data from a SQL log file, then they can go through this blog – How to Recover Data from Log file in SQL Server Effectively?

Before proceeding with the SQL Server log file viewer process, users first need to find out the location of the SQL log files. So, to find out where the log files are stored, simply follow the steps below:

1. Open SSMS & Connect to SQL Instance.
2. Go to Management >> SQL Server Logs.
3. Now, View the Log & Archive Logs simply.

How to View Log File of SQL Server? Methods Explained

In the following section, we will understand the best ways how to open, check, and SQL Server read transaction log. We will also understand how these ways will help retrieve information about the data which had been altered.  As we have already discussed the steps to find the location of SQL Server log files, let’s move ahead. From the start of SQL Server or manual log file recycling, users can use these logs to view recent activities. So, let’s get started with the methods to check SQL Log files easily!

1st Method – Using xp_readerrorlog() Function

Users can use the extended xp_readerrorlog process to find the current location of the log file.

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL
GO

Now, we have some predefined guidelines to view log file of SQL Server as mentioned below:

• End-time
• from time
• Search string1
• Search string 2
• Sort results – Ascending (N’ASC) or descending (N’Desc)
• Error log file: value 0 for the current, 1 for Archive#1, 2 for Archive #2
• Logfile type: Value 0 for SQL Server error log, 1 for SQL Server Agent log

Just as an example: The log file location is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG. Now SQL Server read transaction log is much easier.

2nd Method – With SERVERPROPERTY() Function

Search the SERVERPROPERTY query to find the location of your SQL Server Log files & learn how to open log file viewer SQL Server.

SELECT SERVERPROPERTY('ErrorLogFileName')  AS 'Error log location'

#Approach 1: Use Log File Viewer in SQL Server Management Studio

Basically, this method is exclusively popular for learning how to check database logs in SQL Server to open and view the information about following logs in SSMS:

• Audit Collection
• Database Mail
• Job History
• Data Collection
• SQL Server
• SQL Server Agent
• Windows Events

Its prime function of Log File Viewer is to provide the report of activities taken place in SQL Server Management Studio. In fact, one can open the Log File Viewer wizard in different ways on the basis of  information that you want to check. Now, go through the instructions to view log details in SQL Server.

How to View Log File of SQL Server Via Log File Viewer

Step-1. Open Microsoft SQL Server Management Studio application. Here, we are using SQL Server 2014 environment for reading SQL Server Error Log.

Step-2. Connect to Server windows pops-up. Here, you need to select the Server Name and Type of Authentication. Afterward, click on Connect.

Step-3. In Object Explorer, go to Management as shown in the screenshot to examine or read log file of SQL Server 2014.

Step-4. Now, move to SQL Server Logs option to view log file of SQL Server.

Step-5. Now, Right-click on SQL Server Logs and select View >> SQL Server Log sequentially.

Step-6. All the Log summary displayed on Log File Viewer window. Here, you can select other logs such as SQL Server Agent , Database Mail from the left panel to check its information too.

#Approach 2: View Log File of SQL Server Via. Undocumented fn_dblog()

Originally, the function fn_dblog() is used to extract data from the Transaction file of SQL Server for forensic purposes. This function is used to SQL Server read transaction log and analyze each log event performed on the database table. So, let’s check out how to read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 editions with the help of this function. To understand the working of the function properly, we will consider using an example to get a better idea.

Steps to View Log File in SQL Server Using Fn_dblog()

Step 1: Suppose we have a table named ‘Employee’. So, first, view the values of the table using the following T-SQL. The command below will help with the same:
Select * from employee.

Step 2: Afterward, alter the table data using update command. For this, execute the query:
Update employee set department ='IT' where emp_name = 'jeevan'

Step 3: Again, view the table values using the Select Query. Now, you can see a modified table.

Step 4: Run the fn_dblog function according to the need to view log file of SQL Server. Here, we execute the query to check out the time when update operation was executed.

Select [Begin Time], [Transaction Name] from fn_dblog(null , null) where [Transaction Name] = ‘Update’

Step 5: In a situation, when you want to analyze all the logs such as Delete etc., then run the following T-SQL query.

Select [Begin Time], [Transaction Name] from fn_dblog(null, null)

However, there are some consequences attached with fn_dblog(). Actually, this function only provides the time of the query when it was committed instead of which data entry gets affected. Due to this, it becomes cumbersome to find out which table data get altered. 

This problem can be overcome with the third technique, where the user can view the log file of SQL Server without any hassle. Apart from this, both the described techniques can run in SQL Server Management Studio only. You cannot proceed with SQL Server read transaction log in offline environment with Log File Viewer and Fn_dblog().

#Approach 3: How to View Log File of SQL Server with Smart Solution to Analyze Transaction Files

Apart from the provided solutions, to get exact information from SQL Log File, take the help of SysTools SQL Log Analyzer Tool. With the help of this advanced log file viewer SQL Server, users can scan and analyze T-log file in human readable format. However, the tool works in Online as well as Offline environment. User can get the information like Transaction , Login Name , Time , Table Name , Query . It is a best software solution that answers the question – how to read SQL Server Transaction Log file.

 

Related : How to Fix Log File Corruption – Step-By-Step Guide

In fact, the users view log file of SQL Server, they can export the query in Live SQL Server database environment, SQL Compatible Scripts, and in CSV format. Moreover, the software can read Transaction log file of every SQL Server edition.

Why Learn How to Check SQL Logs? Real-Life Case Studies Examples

Let’s go through the below-mentioned case studies which explain why is it beneficial to be aware of viewing the SQL log files in various ways and why is efficient to use a professional log file viewer SQL Server. Both these case studies are real & based on actual incidents.

Case Study – 1

A DB administrator noticed that the performance of their SQL database is getting slow. Thus, they decided to just view the error log files and find out the issues. After a few failed attempts, they relied on the advanced tool which helped them reach the roots of the problem. In the research, it was found that a specific query was consuming way too many resources than it was supposed to. The database started working smoothly right after fixing that query.

Case Study – 2

Recently, in a finance firm, the SQL administrator observed an unusual activity. He saw that several failed login attempts were happening from an unknown IP address. Because of viewing the log files in time, the IT team was able to take the respective security measures and counteractions. Any kind of delay would have been risky for the entire firm in the name of a security breach. Thus, being aware of the method to read transaction log SQL Server is proven helpful.

Safety Measures on How to View Log File of SQL Server?

Now, we have to understand the tips & tricks to either prevent critical issues from occurring at the time of viewing the log files or fix them after happening.

• Regular Log Audits: Frequent log reviews and audits can be easy with automated alerts which send signals for potential threats and updates for critical errors.
• Log Shipping & Backup: A robust backup and recovery strategy for SQL log files helps in handling the errors users might face while trying to learn how to check logs in SQL server.
• Optimizing Log File Size: Always keeping the log file within the nominal size, makes it easier to view, share, search, & execute other operations.
• Latest Security Patch Updations: Users must understand that updating the SQL Server on a frequent basis can help minimize the risk of any errors in the server or operation failure.
• Best Use of SQL Server Management Studio: For manual ways, utilizing the SSMS to its fullest can help users view the log database files along with the advanced tool.

Conclusion

That’s all about how to View Log file of SQL Server. Now, go through the above-mentioned methods and opt for the best MSSQL viewer that is suitable for you and examine the SQL Server Transaction Log file without any hassles at all. Make sure that you are confident while executing the manual method or simply go for the automated solution.

Also Read: SQL Server Transaction Log Forensics

Frequently Asked Questions