[dba-SQLServer] full transaction logs

Hi Susan and Arthur,

The transaction is only truncated if you:

1. Set the database to SIMPLE RECOVERY MODEL. This recovery model equals the
obsolete command SET TRUNCATE ON CHECKPOINT ON, meaning that the log is
automatically truncated when a checkpoint is performed (normally every 1
minute). However, setting the database to SIMPLE RECOVERY MODEL will not
allow you to make backups of the transaction log - so this is not a
reasonable choice for an OLTP-database.
OR:
2. Perform a manual truncation of the log issuing the command BACKUP LOG
<mydatabase> WITH TRUNCATE_ONLY. This command should only be used as an
emergency if the database log for some reason is bloating.
OR:
3. Perform a log-backup at regular intervals. Notice that only a log-backup
will truncate the log, a full or differential backup won't.

Of these options I normally would prefer the last for an OLTP-database.

If I'm not wrong this may be an answer to Arthur's statement: "I don't know
what I'm doing right."

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur
Fuller
Sendt: 10. september 2008 22:32
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] full transaction logs

I know that some people have received a message that their transaction log
is full, but I personally never have. I don't know what I'm doing right.
Recently we changed our strategy on transaction logs. Now we back up the log
(only the log) four times a day and nightly do a dbcc shrinkfile, so the
living log is peanuts compared to what it used  to be. Before I got here
there was a nightly process that dropped a table and then recreated it and
did thousands of inserts. As a result, the log file was huge -- way larger
than the 18 gig db itself. We changed that process dramatically and the log
file shrank dramatically too.
Arthur
On Wed, Sep 10, 2008 at 4:33 PM, Susan Harkins <ssharkins at gmail.com> wrote:

> Are full transaction logs still a problem in 2005 and 2008? I'm just
> wondering if they've done anything automatic to warn the administrator or
> to
> avoid the problem altogether.
>
> Susan H.
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com