FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: ZIGLIO Frediano [mailto:Frediano.Ziglio AT vodafoneomnitel.it]
> Sent: October 25, 2002 4:16 AM
> 
> I tried with command
> 
> 1> dump tran tempdb to 'output.txt'
> 
> But it reply with strange error...

Frediano, 

I think "dump tran with no_log" is your friend.  That's the old syntax and
should still work.  

Sybase's design uses a write-ahead log.  Your query is written to the
transaction log first, then applied to the data, then marked as "done" in
the log.  Only then is the transaction considered "committed".  Not until
then can it be removed from the log.  

Microsoft moved the log out of the database and put it in the filesystem,
but its role and purpose were unchanged.  

This design allows the server to "know" the state of the database at any
point in time.  After a power failure, the system can examine the log,
compare it to the tables, and "roll forward" any transaction that was
written to the log but unapplied.  Conversely, any partially written log
records can be discarded.  (BTW, I think this is the most important feature
separating proprietary databases from free ones.)  

The log continues to grow until it is "truncated", meaning that committed
records are removed.  If the truncation operation saves the records to a
file, they can be used later as part of an incremental restore operation.
We have many such "warm standby" machines here: they have a copy of the
production database, and periodically (say, every 10 minutes) the production
log is truncated and applied to the standby.  Data loss is thus constrained
to the truncation window.  For more information, rtfm; look for "Checkpoints
and the Active Portion of the Log".  

In your case, you don't need a backup, you just want your space back.  That
was my slogan when I was doing sysadmin work, "The only good disk is an
empty disk."  Empty disks are quick to scan, quick to back up, are never
full.  No one ever loses a file on them.  They're ideal, wouldn't you say?
But I digress.  Again. 

In such cases, where you just want to minimize the size of the tranlog and
not worry about backups, the "trunc. log on chkpt." database option (cf.
"Setting Database Options") is very handy.  Every minute or so the system
will inspect the log and automatically remove committed transactions.  

Regards, 

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.