Can the transaction log in SQL Server 2008 Express edition be viewed in text?

Question

Is it possible to read the transaction log in text/table format, and if so, how can I do that?

Answer 1

The following query will output the full contents of the current log in tabular format. If you only require a portion, replace the NULLs with LSNs.

SELECT * FROM sys.fn_dblog(NULL,NULL)

If th.e data is no longer in the log, then the following query will return the contents of your log backup.

SELECT * FROM fn_dump_dblog (
        NULL, NULL, 'DISK', 1, 'D:\SQLskills\FNDBLogTest_Log2.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);

Yes, you DO have to specify all 63 default values. These represent files and you can have up to 64 files in a backup set.

Paul Randal blogged about this topic here.