Wednesday, March 28, 2012

Log full

Hi! Please help with the following:
Our disk space is limited. I set the database recovery model to
"FULL", and backup transaction log every hour between business hours.
But the disk is always full because the transactions log growth. Do
you think increase the frequency of transaction log backup or backup
transaction 24 hours instead of only business hours will solve the
problem?
Thanks!!"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!|||"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!

No comments:

Post a Comment