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):
- -1 = Full log records plus hex dump of the current transaction log’s low, plus checkpoint start, Database Version and MAX XDESID.
- 0 = Minimal information providing the LSN, Operation, Context, Transaction ID and if a Log Block was generated
- 1 = Same data as output type 0 plus the record length, previous LSN, and a description of the log record
- 2 = Same data as output type 1 plus the Allocation Unit ID, object name, SQL Server Page, Slot location within the page, and locking information
- 3 = Full log record
- 4 = Similar data as output type 1 including a hex dump of the log record
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)