Introduction
SQL Server maintains all records of the database modifications and transaction done by the user. The SQL server keeps all database transactions in a file entitled as Transaction log or TLOG. The transaction log LDF file is specific for SQL Server database and there is one single transaction log per SQL database. In case, if system gets failed, then transaction logs can be used as a backup, and will help to retrieve the database in its original form.
Sometimes, database has access of more than one user, any modification or updation being done is necessary to know by the user and wants to view all the transaction log details performed in the SQL server.
Hence, there is an inbuilt function named as fn_dblog() available in SQL server by which a user can access the TLOG. The function will let you know the time of transaction activity being performed in the form of various parameters. But the retrieved LSN Number using fn_dblog() is not in human readable form and fn_dblog() function does not recover the transaction log data until the backup of transaction log is not available.
Therefore, SQL Log Analyzer becomes the rescuer, which allow you to view SQL Server transaction log. Not only this, the SQL log reader recovers the transaction log and list out all the activities being done in the logs.
In the upcoming section, I will describe how SQL log file analyzer made easy for users to record all transaction activity and make easy recovery of transaction logs.
Overview of fn_dblog()
The fn_dblog is an undocumented function that enables the user to read data from the transaction log file. The fn_dblog requires an initial LSN and final LSN for a database transaction. And, Null is a default function in fn_dblog and it will return all log records from the transaction log file. With the help of fn_dblog function, a user can also check the internal SQL Server function.
To view the full transaction log file, run the following code on the SQL Server:
select [Current LSN],
[Operation],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[SPID],
[Begin Time]
From fb_dblog(null,null)
Finally, a transaction log table will appear on the screen containing following information like Current LSN, Operation, Transaction Name, Transaction ID, Transaction SID, SPID, Begin Time.
Overview of SysTools SQL Log Analyzer
SQL Log Analyzer is the best choice to view and examine transaction log file. Besides fn_dblog function, it shows the result, that can be easily readable and understandable to the end user. It provides several features that focus on the different part of LDF transaction. Some of the highlighted features are listed below:
- Evaluate transaction log file carefully & recovers corrupted LDF file also.
- Shows all the components such as transaction, time, name, table name & query.
- Analyze and read all queries like INSERT, DELETE, UPDATE, DESC, ALTER, etc
- Supports a number of data types such as Datetime 2, hierarchyid, geometry, etc
- Preview SQL server database queries as SQL script or as CSV format
- Log Analyzer work in both the environment: Online and Offline SQL database
- Shows the running SQL database queries record in transactions log
- Compatible with SQL server 2016,2012,2014,2008 and 2005 LDF Files.
Recommendation: It is advisable to use SQL transaction log reader in comparison to fn_dblog. As the software able to recover transaction logs with listing of all modified and updated transaction activities like LSN Number, Transaction Name, ID etc. fn_dblog function
Analysing Transaction Logs using SysTools SQL Log Analyzer
Analyze the transaction log details with the Log Analyzer tool in few easy steps:
Install and run Log Analyzer tool in your system.You will get two modes, Online and Offline Database. Browse your file after selecting the mode and the selected file will preview all the transaction activity.
Click on Export and migrate SQL log files to the new destination.
Wrapping Up:
In the above-discussion, we conclude that the information contained in the transaction log is valuable and its help in data loss or fraud issue. The transaction file showed by fn_dblog() is readable but a non-technical person unable to understand its complete working. In addition, we have also discussed a professional solution that will help to explore and recover the transaction log file in an absolute manner. Along with this, the data shown by the LDF Analyzer is more understandable by the end users.
EmoticonEmoticon