[dba-SQLServer] full transaction logs

I completely agree,  In my environment we now also have VLDB's and in those
systems the transaction log is typically between 15-30gb large depending on
which system we're talking about... I have an automatic alert that fire's
off when the transaction log hits 60% of it's total size and performs a
backup.  This effectively prevents the log from over-growing.  At night I
perform a Differential backup and  this allows me to drop the transaction
logs within 1week so that I don't eat up too much disk space.  I am using
Red-Gate's Sql Backup so I compress all my backups, for my largest database,
a compressed backup generally looks like 250gbs and takes between 2-3hrs
depending on network traffic.

I also do have a script for shrinking the log files that fires off once a
week, but generally this does not occur to often (it's also set on an alert)
the purpose is that this should not fire often if all the other backups are
occuring at regular intrevals, over-shrinking your file then re-growing it
on NTFS causes huge disk fragmentation if you are using SAN disk, then the
amout of fragmentation depends on the vendor of SAN you are using...



-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Wed, Sep 10, 2008 at 4:09 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

> Just a second comment for Arthur:
>
> You wrote: "Now we back up the log (only the log) four times a day and
> nightly do a dbcc shrinkfile"
>
> To my mind a DBCC SHRINKFILE is an emergency command which should NOT be
> included in an automated administrative task.
>
> DBCC SHRINKFILE will free truncated file-space to the operating system.
> This
> may be a reasonable action if the log-file has been bloating. Otherwise it
> certainly is not: freeing truncated file-space to the operation system will
> force SQL Server to allocate new file-space for the log as needed, and this
> will be a great invitation for file fragmentation at the disk level.
>
> Asger
> -----Oprindelig meddelelse-----
> Fra: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger
> Blond
> Sendt: 11. september 2008 00:18
> Til: 'Discussion concerning MS SQL Server'
> Emne: Re: [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
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>