Ingenious Guide to View Log File of SQL Server

  author
Written By Chirag Arora
Anuraag Singh
Approved By Anuraag Singh  
Published On April 12th, 2023
Reading Time 8 Minutes Reading

SQL Server Logs

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 viewing SQL Server log files, some users face trouble. We are sure that you must be aware of how good the MS SQL Server database is for both beginners as well as expert users.

But, Digital Crimes Can Takes Place In SQL Server Too!!!!

Nowadays, Ex-employees or hackers intentionally modifies the values of databases in order to damage the organization assets. And it becomes difficult to analyze or examine who is the culprit manually. As a result, the Organizations run into  big trouble.

Hold On!! There Is A Good News

In SQL Server, there is a transaction Log file that keep records of all transactions & modifications in database executed on a database in a Microsoft SQL Server. By reading the Log file, one can easily check who deleted data from table in SQL Server database. Plus, it is used by forensic investigator to examine SQL Server Transaction Log and view & check every log detail in a detailed manner. In short, with SQL Log file, it becomes easy to find out which query performed on which table at what time.

Here, we are going to answer how to view log file of SQL Server 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 user want to restore the deleted query from a log file, then they can go through this blog – How to Recover Data from Log file in SQL Server – A Complete Guide .

Now, first of all, users need to find out the location of the log files. Then only they can proceed with a desired solution. So, to understand where your log files are, simply follow the below steps:

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

logs location

Methods Use For How to View Transaction Log File of SQL Server

In the following section, you will understand how to open, check and read transaction file to retrieve information about the data which had been altered.  Before we move on towards the solution, let’s look at the ways to find the location of SQL logs easily. So, let’s get started!!

As users saw the location of the 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.

1st Method – 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 for this procedure 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 users can see these files easily.

xp function

2nd Method – SERVERPROPERTY() function

Search the SERVERPROPERTY query to find the location of your SQL Server Log files.

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

server property function

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

Basically, this method exclusively used 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.

Open SSMS

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

Establish Connection

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

Choose Management

Step-4. Now, move to SQL Server Logs option.

read transaction log file of sql server 2008 r2

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.

Select View to read error log file

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

Originally, the function fn_dblog() is used to extract data from Transaction file of SQL Server for forensic purposes to analyze every log event performed on the 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.

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

Step 1: We have a table named as ‘Employee’. So, first view the values of the table using the following T-SQL.
Select * from employee.

View Table Data

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

Alter the table

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

Updated Table

Step 4: Run the fn_dblog function according to the need. 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’

read transaction log file sql server

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 provide the time of the query when it was committed instead of which data entry gets affected. Due to which, it becomes cumbersome to find out which table data get altered.

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

#Approach 3: Use  Smart Solution to Analyze Transaction File Easily

To get exact information from SQL Log File, take the help of SysTools SQL Log Reader Software. With the help of this software, user 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.

Download Free SQL Log Analyzer

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

In fact, after viewing the log file of SQL Server, user 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.

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 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

How to open SQL Server database Transaction Log file (.ldf) in readable format?

Try SQL Log Analyzer tool to easily scan and read the Transaction .ldf file records.

How to view SQL Log file in SSMS?

Use Fn_dblog() function to read the details of transaction in SQL Server.

Can I read log file of SQL Server 2008?

With the help of SQL Log Viewer, one can read .ldf file and view Transaction, Transaction time, Table name and Query of Microsoft SQL Server 2017, 2016, 2014, 2012, 2008 and SQL Server 2005

Is it possible to examine SQL Transaction Log file?

Yes, with the help of mentioned workaround, one can easily examine SQL LDF file.

  author

By Chirag Arora

Having around 9+ years of experience in technical writing. Knows about the core technical areas. Also, provides easy and reliable solutions to resolve difficulties faced by users while working with different platforms.