Reading SQL Server Transaction Log

Reading SQL Server Transaction Log article image

Ever needed to view the transaction log for a database in SQL Server? DBCC LOG is one of several undocumented DBCC commands for SQL Server. It can provide essential information regarding changes to data in the database. Undocumented as is, use it at your own risk 😉

DBCC LOG('databasename', type_of_output)

The LOG accepts two parameters. The first is where you specify the name of the database whose transaction log to view. The second parameter is the type of output you want to review. The value is an int between -1 and 4 where default is 0. Those are the output types (source):

There is also another undocumented command to extract data from the transaction log – thefn_dblog function. It takes two parameters: starting LSN and ending LSN to process. LSN is the Log Sequence Number wich is used to identify every record in the SQL Server transaction log. NULL as parameter means process everything. Note that the query below can be slow to execute as it selects every column.

SELECT * FROM fn_dblog (NULL, NULL)