Re: Viewing SQl transacion logs...

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ron Hinds (__NoSpam__ron_at___ramac__.com)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 14:17:56 -0700

"Brad Pears" <donotreply@notreal.com> wrote in message
news:%23g2cKtSvEHA.2804@TK2MSFTNGP14.phx.gbl...
> 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?
>
> Thanks,
>
> Brad

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.

Relevant Pages

  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Advantages of an SQL 2005 cluster from transaction point of view
    ... Clustering will not effect the transaction count which your SQL Server ... Cluster service is designed as a high availability solution. ...
    (microsoft.public.sqlserver.clustering)
  • Re: sqltransaction timeout
    ... SQL Server places exclusive locks on all of the resources ... These locks are then held until the ... transaction either commits or rolls back. ... SQL Server does not even ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)