3

I mean the way to open the LDF file and read/analyze it.

Now, I have ability to extract the backup LDF file, but it seems that the LDF file is SHARE_DENY_READ when the database is working.

But the other 3rd-party software like Lumigent Log Explorer，how could they open the file?Does this kind of software read LDF directly?

BlackThought
  • 31
  • 1
  • 2
  • 3
  • With SQL Server, **you should never EVER** fiddle with the MDF/LDF files directly. **Just don't do it!** If you want something from SQL Server, ask the SQL Server engine to do it for you. – marc_s Oct 13 '11 at 04:44
  • @marc_s: What's you suppose to say?The GoldenGate starts from Oracle Redo Log,and I almost know the structure of the SQL Server Transaction Log. – BlackThought Oct 18 '11 at 02:21
  • Try to use log analyzer tool I searched one such software that might help you. Check out this link:- [http://www.sqlrecoverysoftware.net/sql-server-log-file/](http://www.sqlrecoverysoftware.net/sql-server-log-file/) –  Mar 27 '14 at 15:55

3 Answers3

2

SQL Server transaction log format is not documented and therefore can’t be used to read data from it directly.

There are tools such as ApexSQL Log that can read the transaction log but it’s only because they probably spent a ton of time reverse engineering its format.

Options for reading are to:

a) figure the format on your own (not recommended) b) get yourself a third party tool c) using functions such as fn_dblog that are also not documented but can give you some details.

Iza Pastoor
  • 192
  • 1
  • 4
1

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

Note that the provided solution is not affected by whether you read an online transaction log or not - there are no locks that prevent the process of reading and analyzing the transaction log

Disclaimer: I work as a Product Support Engineer at ApexSQL

Ivan Stankovic
  • 1,602
  • 18
  • 13
1

I'm not sure I'm understanding your question, but yes these 3rd party tools read the ldf file directly.

You can also use the DBCC LOG command, but it is undocumented and the output is a bit cryptic.

  • Sorry about my English, because it is not my first language.I mean I am able to analyze LDF file, I know the LDF structure, but it just couldn't be opened when database is starting. – BlackThought Oct 18 '11 at 02:19
  • To know what transactional detail kept in a .ldf file you can try [SQL .ldf file viewer](http://www.mdfviewer.com/ldf/) which is able to open, read and analyze all the information related SQL database transactions. – Jason Clark Sep 27 '16 at 08:53