Microsoft SQL Server application is one of the biggest waves in the relational database management system and handles huge database in a well-structured manner.
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 who is the culprit manually. As a result, the Organizations run into big trouble.
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 read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.
#Approach 1: Use Log File Viewer in SQL Server Management Studio
Basically, this method exclusively used to view the information about following logs in SSMS:
SQL Server Agent
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 Transaction file.
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 read log file of SQL Server 2014.
Step 4: Now, move to SQL Server Logs option.
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 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.
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. 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 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 .
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.
That’s all about on how to View Log file of SQL Server. Now, go through the methods and opt the best that suitable for you
About The Author
An entrepreneur, technical analyst, a writer with innovative and authentic thoughts when it comes to technology. Renders brilliant solutions to deal with issues users face while working with technology. Having amazing knowledge in technical arenas in numerous field.