3

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

Is this possible?

Thanks

Jey Geethan
  • 2,235
  • 5
  • 33
  • 60
  • Is this a one off ad-hoc requirement or a process you wish to perform regularly? – John Sansom Jan 27 '10 at 13:43
  • This is a process I would like to perform regularly. Say I want to produce a report about how many times a particular row was changed in a month. – Jey Geethan Jan 28 '10 at 08:05

4 Answers4

3

Late answer but I hope it will be useful to new readers…

One more function you can try is DBCC LOG but unfortunately this is undocumented function same like fn_dblog.

Problem with transaction log in SQL Server is that it was never meant to be used for this but only to allow point in time recovery and transaction properties.

There is a commercial log reader from ApexSQL that you can try.

Here are also couple similar posts that might get you in the right direction.

Read the log file (*.LDF) in sql server 2008

SQL Server Transaction Log Explorer/Analyzer

Community
  • 1
  • 1
Ryszard Wojchiechowsky
  • 318
  • 2
  • 4
3

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.

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • I would like to take the improbable route. Any pointers on how can I start this? (Links or something?) – Jey Geethan Jan 27 '10 at 10:07
  • Have a look at https://www.blackhat.com/presentations/bh-usa-07/Fowler/Presentation/bh-usa-07-fowler.pdf slide 21 onwards - you will soon realise how hard it is to decode. – Andrew Jan 27 '10 at 10:38
  • And I am guessing that the way we get old transactions is going to be different for MS SQL server and Oracle. Is this true? If so, I guess I have to leave this and get going. Anyways more links will be helpful. Thanks mate. – Jey Geethan Jan 27 '10 at 12:37
  • Yes, Oracle will be entirely different. – Andrew Jan 27 '10 at 12:50
0

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

IordanTanev
  • 6,130
  • 5
  • 40
  • 49
0

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

John Sansom
  • 41,005
  • 9
  • 72
  • 84