How to read sql server2000/2005 transaction log user friendly way?

[Tridip Bhattacharjee]

we know that when we insert data,update data and delete data against any table then that record is stored in transaction log of sql server. so how can i see those data in a very friendly way. please help with sample sql code.

tbhattacharjee

[David Mujica]

As far as I know, there is no way through SQL to read the transaction log of any database. You might find some tools out there to read the transaction log, but you would need to be very careful that you don't interfere with the running database. Why don't you create an audit table that would hold these before and after values of the data ? You can implement this logic by creating triggers that would write to this table during an insert or delete. This is very standard practice when developing a system that requires auditing functionality.

[Netblue]

First, apologies for hijacking this thread... What kind of table layout do you use for the Audit tables? Right now, we have some old legacy apps that are being rewritten and we are going to be adding auditing to them via triggers. Is there a good / standard practice format for the tables?

[David Mujica]

The format I've seen before is typically an exact copy of the original table being audited along with some additional info such as datatime, userID and transaction code (Insert,Update,Delete). We used to name these tables something like EMPLOYEE_AUDIT. By keeping the format close to the original table format, it made it easier to implement. Take into consideration the size of these audit tables ! I have seen audit tables grow at 500MB per month, which can cause serious performance drain on your DB server. Google around for other ideas on how to implement auditing in your new system.