How to View SQL Server Database Transaction Log File

Introduction to SQL Server Transaction Log File

The SQL Server transaction log file is a file that contain all the transaction that is performed on a database. It is not an easy task to view what is stored in SQL Server transaction log file. Here I have mentioned some methods that may be helpful in viewing a transaction log file.

• fn_dblog()
• fn_dump_db_log()
• DBCC PAGE()
• DBCC LOGINFO

Note: Even Microsoft does not provide any type of documented tool. But, there are some undocumented tools that might be helpful to view a SQL Server transaction log file.

1. Using fn_dblog()

The fn_dblog function is an undocumented SQL Server command which is an example of table-valued function. When you use this function, you will be able to view the information related to the log file. The function fn_dblog allows you to view SQL Server transaction log history only when your database is in the online mode.

The fn_dblog function allows only two parameters to view the SQL Server transaction log files. One is starting LSN and the second is ending LSN. You need to pass the NULL parameter to list all the properties associated with LDF file.

Syntax:

SELECT * FROM fn_dblog(NULL,NULL)

After running this command you can see the properties related to the transaction log file. If you want to see the transactions from the result set returned by the command, then you need to execute the given commands.

I. If you want to see the transactions in a row, then you need to run this query which will show all the transactions related to the row transactions.

Syntax:

SELECT FROM [parameters], sys.fn_dblog
    (NULL, NULL)
    Where operation IN
    (‘LOP_INSERT_ROWS’);

II. If you have some deleted LDF files and you want to see the transactions from that file, then you need to execute the following query.

Syntax:

SELECT FROM [parameters], sys.fn_dblog
    (NULL, NULL)
    Where operation IN
    (‘LOP_DELETE_ROWS’);

2. Using fn_dump_dblog()

The command fn_dump_dblog is also an undocumented command provided by MS SQL Server. The fn_dump_dblog can read both the online transaction logs and log backups and it also accepts 68 parameters.

Syntax:

SELECT FROM fn_dump_dblog

To get the information from log files after performing the fn_dump_dblog function you need to read out again the hex values. After this you will need to manually reconstruct all the row values, also you need to resurface the whole state for UPDATE and BLOB operations.

3. Using DBCC PAGE()

The DBCC PAGE command is used to obtain the accumulated data in SQL Server database. If you are using the DBCC PAGE command and want the information returned to the front-end, then you need to be set a trace flag. The DBCC TRACEON (3604) command must be executed before running the command DBCC PAGE.

If the information is to be sent to the errorlog, then you can use the DBCC TRACEON 3605 flag rather than 3604. The DBCC PAGE command is used to read out the content of database in only online mode.

Syntax:

DBCC PAGE( {‘db_name’ db_id}, filenum, pagenum, [, printopt={0 1 2 3} ])

If you changed the print option the offset table and rows also will display. Rows will display in reverse binary from how they are stored in the database. Row length errors and offset table problems can be discovered using this command.

Note: The DBCC LOG() command and trace flag 2537 can be also used to view the transaction log information. The DBCC LOG() command will never show the detailed information and trace flag 2537 will display all the active and inactive log files too.

4. Using DBCC LOGINFO

The statement DBCC LOGINFO can be used to view that how many virtual log files are present in SQL Server database and what the actual size of virtual log files and how much it has been used.

When you perform the DBCC LOGINFO on a transaction log file, then this command shows the these properties: RecoverID, FileSize, FileID, Status, FeqSeqNo, Priority, CreateLSN, StartOffset.

Alternate Option to View SQL Server Transaction Log File

In the starting of this blog, I have cleared that the Microsoft doesn’t provide any straight forward method to view a transaction log file. To avoid the lengthy manual method, it is recommended that you enlist the help of alternate solution like SQL Server transaction Log viewer tool to analyze SQL transaction log.

Conclusion

In the present article we have discussed about, how to view a SQL Server transaction log file, while Microsoft SQL Server doesn’t provide any type utility to directly view the transaction log files.