How to query from log files (.ldf) of Sql Server 2005 Express edition?

Question

I want to do this, because I would like to know how many times a particular row has been changed.

Is this possible?

Thanks

Answer 1

Hi, you can use this program to do it http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Answer 2

Reading the log file either takes a commercial tool, or an incredible amount of SQL internals knowledge to achieve. You can see some of the raw output by using: Select * from ::fn_DBlog(null,null)

Actually decoding to find the same record being altered and ensuring any alteration was committed etc would be a difficult task to put it lightly. So it is 'possible' but not very 'probable' that you will be able to do it.

If you need that functionality within a database then you should be looking at triggers / logic within the code.

Answer 3

Consider using SQL Server 2008.

There is a feature new to SQL Server 2008 called Change Data Capture that does exactly what you require, that is to track data modifications over time.

Looking to inspect the log file in order to track changes is not a wise practice. Doing so will provide you with a limited history, the scope of which would also be dependent on the Recovery Model that you use for your database.

You could "roll your own" solution with a small amount of development, by using a log table and populating it using SQL Server Triggers. The suitability of such a solution is of course dependent on your business case.

Take a look at the following TechNet article for some interesting reading:

Tracking Changes in Your Enterprise Database