MS SQL 2008 HowTo read from log

Question

Hi !

I have a table with many rows, is there any way to find out when a concrete row has been inserted? (I don't have create/update time columns)

Thanks

Answer 1

No, generally the log is not there for the user to analyze. it is also not organized in a way leading easily to this information UNLESS you would decode it from the start. Plus, as logs get deleted after every backup.... you may not know even then, unless you never make a backup.

The only reason logs exist is to be there to roll forward in case of a disaster, to allow your last backup to get up to the point of the log.

In generally, if you want that information as part of a table, make it part of the table (.e. insert create/update timestamp columns).

Answer 2

Examining the log to read this kind of information can be done on a single log entry kind of basis, but the format remains undocumented and really not trivial to decode. I would only look at it for pure interest, or forensic purposes.

If you want to see how nasty it gets to do a simple transaction log entry, have a look at http://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/

If you want change information, you either need to build it in to the software, or because you are using SQL Server 2008, there is a new feature in that release called Change Data Capture ( http://msdn.microsoft.com/en-us/library/bb522489.aspx ) which can do this for you in effect.

Given you are on SQL 2008, I would recommend the CDC feature as the route to go.