Transaction log size after backup.

[D. Buck]

I have a MS SQL 2000 dB that is 3GB, the transaction log is about 2GB.
I do a full dB backup every night. My question is, shouldn't the
transaction log shrink down to next to nothing after a full dB backup?

D.
p8oust7eh+

[nigelrivett]

Re: Transaction log size after backup.


No - the tr log backups are independent of the full backup.
If a full backup gets lost or corrupted you can go back to the
previous backup and still use the sequence of log backups up to the
current point.

To allow the log to truncate you will have to do a tr log backup.
In fact if you have the database in full recovery mode then you should
be doing tr log backups more often.
Maybe it should be set to simple?

--
Posted via http://dbforums.com

[A.M. de Jong]

Re: Transaction log size after backup.

Hi D,

You have to understand 2 facts:
1. Truncate of the transaction log; This means that the transaction log
removes committed transactions.
The log does not shrink but the space now is reused.
2. Shrink of the transaction log. This means that the unused space is given
back to the OS.

If you don't take measurements with this your file will grow continuously.

The truncate happens after a transaction log backup. And at this time you
will be able to shrink the transaction log; You can make this a part of
your maintenance plan; you can do it manually or you can make it a part of
your job.
You can't shrink before you truncate (might have just a little effect).

Greetings

Arno de Jong, The Netherlands.


"D. Buck" <triumpht5@yaho o.com> wrote in message
news:f433d5cf.0 307091220.3d363 6af@posting.goo gle.com...[color=blue]
> I have a MS SQL 2000 dB that is 3GB, the transaction log is about 2GB.
> I do a full dB backup every night. My question is, shouldn't the
> transaction log shrink down to next to nothing after a full dB backup?
>
> D.
> p8oust7eh+[/color]