DBCC LOG is one of several undocumented DBCC commands for SQL Server; it allows you to view the commands in the Transaction Log file.

Note that use of the DBCC LOG command (and all other undocumented DBCC commands) against a production database instance is executed at your own risk.

DBCC Log Parameters

The DBCC LOG only accepts two parameters.

• The first is the name of the SQL Server database whose transaction log you want to examine. This can also be identified as the database ID.
• The second parameter is the type of output you want to review. The output type can one of six different values (see below); if you do not specify an output, type 0 is used by default.

Output types are described as follows:

-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

The DBCC LOG command is fairly simple. Use the DBCC LOG statement as shown below in Example 1 to get basic info from the SQL Server database.

DBCC LOG Example 1

Basic DBCC LOG statement

DBCCLOG(SQLServer20058R2)

It is easy to get more specific info from the SQL Server transaction log as well. See Example 2 below.

DBCC LOG Example 2

Detailed transaction log info

DBCCLOG(SQLServer2005R2, 4)

The SQL Server DBCC LOG command works well to gather current transaction log data but is not effective in reviewing data from the transaction log that has been saved to a backup file. In addition, if CHECKPOINT has been triggered and the transaction log data has been overwritten review of this info will be rather useless.

If you would like to see transaction log data over time, you will need to log the data to a table within the SQL Server. The DBCC LOG statement must be wrapped within an EXEC or sp_executesql call for the command to succeed. This cannot be accomplished by only specifying just an insert statement with the DBCC LOG command as the source. This is detailed in Example 3 below.

DBCC LOG Example 3

Logging data from DBCC LOG into a table for later use

CREATETABLE#LogData
 (CurrentLSNsysname,Operationsysname,Contextsysname,
 TransactionIdsysname,LogBlockGenerationint) 
 
 insertinto#LogData EXEC ('DBCC LOG (SQLServer2005R2)') 
 select*from#LogData 
 droptable#LogData

The DBCC LOG command is a very effective tool that can provide essential info regarding changes to data in the SQL Server database. Remember that it is important to understand what each output type will provide from the DBCC LOG statement, especially when utilizing output type 3 or 4.