-2

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

0

2 Answers 2

7

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.

1
  • I would like to add here that fn_dump_dblog command is undocumenetd command but safe to use. You cannot claim any support from microsoft for this command. Also Please dont run it frequently you can use it for current purpose, it has grave performance implications. It also requires a level of competancy to read output of this command.
    – Shanky
    Jul 10, 2014 at 12:34
0

There are different ways to open an LDF file, and most of them do just that – opens it. It’s tricky to get any human readable information and make a use of it though

To be able to read transaction logs in order to see the operation type, the schema and object name of the object affected, the time when the operation was executed, the name of the user who executed the operation, and more, check out the Open LDF file and view LDF file content online article

Disclaimer: I work as a Product Support Engineer at ApexSQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.