CODEWITHSUNDEEP
sql-servertransaction-log
BlackThought
BlackThought

Reputation: 31

Methods of Reading SQL Server Transaction Log

I want to discuss about the Transaction Log of SQL Server, and I searched around, found some product which is accomplished, for example, Lumigent Log Explorer. But I am still interested about it.

Methods I known: 1.Read directly from physical log file 2.Using database command/query, DBCC LOG or through fn_dblog

Problems/difficulties: 1.Log file structure is hard to reverse engineered. 2.When I do lots of INSERTs, the fn_dblog didn't has all of them, for example, when I INSERT 50000 records, the fn_dblog just has 29616 LOP_INSERT_ROWS records, which means 20384 records are truncated?I don't know the internal logical about fn_dblog, can someone explain it?Does the fn_dblog has limitations?

Glad to hear some researches about SQL Server Transaction Log.

Upvotes: 0

Views: 4494

Answers (3)

Ivan Stankovic
Ivan Stankovic

Reputation: 1602

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

ApexSQL Log is a SQL Server transaction log reader which reads online transaction logs, detached transaction logs and transaction log backups – both native and natively compressed. As an LDF viewer, it’s focused on operations (both DML and DDL, 45 in total), and what was changed by execution of these operations: Open LDF file and view LDF file content

Disclaimer: I work as a Product Support Engineer at ApexSQL

Upvotes: 6

William Johnson
William Johnson

Reputation: 190

As paulsm4 already pointed out - transaction log is not meant to be human readable but there are ways to read it using third party tools.

Only tool that really specializes in this is ApexSQL Log but you can also try Quest Toad

Upvotes: 3

paulsm4
paulsm4

Reputation: 121599

The SQL Server transaction log isn't meant to be "human readable". It's meant to support SQL Server, allowing transactions, read consistency, etc etc.

SUGGESTION: If you really want to understand SQL Server internals (including how the transaction log works), I strongly encourage you to get a copy of this book:

SQL Server 2008 Internals, Kalen Delaney

It's an excellent book; you will learn a LOT of practical and important information. Satisfaction guaranteed!

Upvotes: 1

Related Questions