Viewing SQl transacion logs...

Viewing SQl transacion logs...

Post by Brad Pear » Sat, 30 Oct 2004 05:17:02

Since SQL server keeps track of every modification to a table during a
transaction, if I change a field value in a single row of a table, where do
I look to find the log file that was generated that will tell me what the
old value was and what it is now?

Also, is there a way to have SQL keep track of specifc changes regardless of
whether or not the change was part of a transaction?



Viewing SQl transacion logs...

Post by Ron Hind » Sat, 30 Oct 2004 06:17:56


The SQL Server Transaction logs keep track of *all* changes to the database,
whether or not they were contained in formal transactions (BEGIN TRANS,
etc.). That is, *if* you have SQL Server's Recovery model set to either Full
or Bulk-logged. The transaction logs created are not viewable AFAIK; they
are for restoring the database after a crash, etc. The scenario is: your db
server crashes - you rebuild it, then restore the last full backup, then
apply the transaction log(s) and Bingo! you're back to where you were before
the problem!

Now, as to whether SQL server can track specific changes. The short answer
is yes. But it will require you to study some advanced concepts in SQL Books
Online, like Triggers. Basically a Trigger is code that executes in
repsponse to a SQL statement. So you can have Insert, Update and Delete
triggers on any table to create an audit trail. In the code of a trigger,
you can write the data that is being changed to an audit table along with
the user's name, date and time, etc.


Viewing SQl transacion logs...

Post by Brad Pear » Thu, 04 Nov 2004 06:22:18

Thanks for that. Basically that is exactly what we are doing in our Access
DB right now. However, as you can imagine, the overhead to maintain an audit
trail of changes gets larger as time carry's on. I was thinking that since
we are converting to SQL server, if they were already keepijng those audit
trails, maybe I wouldn;t have to create triggeres to do this type of thing

Thanks for your help...