SQL server

Reading the transaction log using fn_dblog and fn_dump_dblog.

The two undocumented functions fn_dblog and fn_dump_dblog can be used to get some insight into the transaction logs. Fn_dblog can be used to read the active portion of the transaction log and fb_dump_dblog can also read a transaction log backup file. Fn_dblog is somewhat like a detailed version of the DBCC loginfo command.

Since these functions are not documented by Microsoft, use it at your own risk. Although these functions are undocumented, they did show up in the intellisense results ;).

SELECT * FROM ::fn_dblog(NULL, NULL). The two parameters are for filtering the start and end of a LSN number range which should be in a numeric format and not in its usual hexadecimal format. Click here to see how to convert the LSN from hex to numeric.

Some of the beneficial information returned are: LSN numbers, AllocUnitName which can be used to identify table names, [Transaction ID] for grouping transactions, [Transaction Name] to identify Delete/Insert/etc (also check the Description column), [Begin time] for using in the STOPAT option of a transaction log restore (point in time recovery). My DB was in the simple recovery mode. I put in in full mode, and a few more lines were added when I ran the fn_dblog command. The transaction name column showed: ALTER DATABASE OPTION STATE CHAN and the [Begin time] showed the exact time I did the change.

Doing a full backup reduced the number of rows returned by fn_dblog. I then did a transaction log backup and proceeded to read it with fb_dump_dblog (click link more info). The only parameter I defined is the fifth parameter which is the absolute path to my transaction log backup file.

select * from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT, ‘F:\stemp\stanley_test.tlog1.bak’, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

There are also some third party products like Red Gates’s SQL log rescue (which has currently paused development of the product) and apexSQL log which costs around $1299 and varies based on the license.

Advertisements
Standard

One thought on “Reading the transaction log using fn_dblog and fn_dump_dblog.

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s