How to read transaction log of SQL Server?
Generally we do not need to read transaction log of SQL Server database. As they are not intended to be human readable file, there is no straight forward native option available also.
However you may want to use some third party tool to read SQL Server log in human readable format. (For example Apex SQL Log, Redgate SQL Log rescue etc). These tools can be very effective for reading a transaction log file.
SQL Server natively supports two commands to enable us reading the transaction log files. However please note that using these commands does not allow us to read the log files in a very friendly manner and anyone you may have to opt for a third part tool for worst case.
Please refer below for native commands to read transaction log files:
DBCC LOG (Databasename,Option)
Option is a numeric value to determine how descriptive the output should be. This can be anywhere from 0 to 4 with below description:
0 – Basic Log Information (default)
1 – Lengthy Info
2 – Very Length Info
3 – Detailed
4 – Full Example
Example: dbcc log (master,4)
This is another option to read transaction log. Personally I prefer using this one when needed.
DBCC LOGINFO (Databasename)
DBCC LOGINFO (master)
The primary thing to look at here is the Status column. This will tell you what portions of the log are in use (Status is not 0) and which are not in use (Status = 0). Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log.
Also if you do not specify database name in this command (ie. You execute the command as
DBCC LOGINFO only), it will return log information for the database where the session is actively connected to.