Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
10  
What do you hope to find in there? –  Remus Rusanu Oct 15 '12 at 6:31
1  
You should add more detail about what you are attempting to do. The log is pretty complicated and knowing what you are looking for, as @RemusRusanu said, would help us help you. –  Max Vernon Oct 15 '12 at 14:48
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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