It is fairly known to most long term DBAs that DBCC log and ::fn_dblog function are undocumented commands used to read the transaction log. What I learnt in the PASS conference this time was that undocumented - means unsupported AND not used in any white paper or reference material by Microsoft. (This information was given by Kalen Delaney, originally I believed undocumented only meant commands not available in books online but apparently not just so). I am just re documenting these two commands
Method 1:
DBCC Log –The syntax is as below
DBCC log ({dbid|dbname}, [, type={0|1|2|3|4|-1|}])
0 - Default with minimum information
1 - Information provided for 0 + flags, tags, and row length.
2 - More information than the 1 option that includes page ID and slot ID.
3 - Information about the logged event.
4 - Information about the logged event plus the hexadecimal dump.
-1 - Option 4 including Checkpoint Begin, DB Version, and Max XACTID.
The best possible usage of this command possibly is to find out if a specific LSN/transaction exists within the log get an idea of what it did. Some of the commonly known values of the field ‘operation’
Some of the possible values for 'operation', by far the most useful field
LOP_BEGIN_XACT - Beginning of transaction
LOP_BEGIN_CKPT - Beginning checkpoint
OP_XACT_CKPT - End of Checkpoint
LOP_MODIFY_ROW - Row modified
LOP_INSERT_ROW - Row inserted
LOP_DELETE_SPLIT - Rows that are moved as a result of page split.
LOP_COMMIT_XACT - Commit Transaction
Method 2:
The ::fn_dblog function, which seems like a wrapper around DBCC log and returns the same results as dbcc log. Since it is an UDF it is easier to manipulate than the DBCC command.
Select * from ::fn_dblog(null, null) – the two parameters refer to start and end lsn, the default of null, null dumps the contents of the entire log.
Reading the log requires thorough knowledge of how it stores and handles every transaction. It may be interesting to play with but may be easier also to buy tools such as the log reader that comes with Quest Toad for SQL Server or Lumigent log explorer.
It is however interesting to know of these commands and how even those of us with minimal knowledge of internals can use them/perhaps experiment with them and understand more of how the transaction log works. Below are two well documented and most useful usage of these commands.
Most Useful Blog Posts
http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
http://sqlblogcasts.com/blogs/leopasta/archive/2007/08/13/page-splits.aspx