Large Tran log backups

Can someone explain me this? I am doing some research and testing and
cannot understand the behaviour I am noticing.
I have a database data file = 1.6 gig and I have the tran log for
testing purposes set to 101.06MB set to auto growth by 10% unrestricted
file size. I also have an alert set up to take the transaction log back
up when it is over 70% full.
This morning I noticed that tran log back up job was fired twice
yesterday by the alert and the size of the tran log back ups were 3.42G
and 3.48G. When I look at the size of the tran log file it is 101.06MB.
I do not have the Auto_shrink option turned on.
Can someone explain how is it possible to have such a large tran log
backup file when the file itself is only 100MB?
Thanksshub wrote:
> Can someone explain me this? I am doing some research and testing and
> cannot understand the behaviour I am noticing.
> I have a database data file = 1.6 gig and I have the tran log for
> testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> file size. I also have an alert set up to take the transaction log back
> up when it is over 70% full.
> This morning I noticed that tran log back up job was fired twice
> yesterday by the alert and the size of the tran log back ups were 3.42G
> and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> I do not have the Auto_shrink option turned on.
> Can someone explain how is it possible to have such a large tran log
> backup file when the file itself is only 100MB?
> Thanks
>
Are you writing each backup to a new file, or appending to the same file
each time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am writing them to a new file. But I am still not understanding how
the backup file of the tran log is larger than the actual size of the
tan log file.
Thanks
Tracy McKibben wrote:
> shub wrote:
> Are you writing each backup to a new file, or appending to the same file
> each time?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What do you get back when you run these:
RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
Roy Harvey
Beacon Falls, CT
On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
[vbcol=seagreen]
>I am writing them to a new file. But I am still not understanding how
>the backup file of the tran log is larger than the actual size of the
>tan log file.
>Thanks
>Tracy McKibben wrote:|||When I ran the first command here is what I got ....
Data file size = 1751777280
Tran log size = 5437521920
Please help me understand how could the tran log get that big when I
have an alert setup and also how is it that it is only 100MB now. I do
not have the auto shrink option turned on.
On the second command, here is what I got, providing you the info that
you would care
BackupSize FirstLsn LastLsn
CheckpointLsn
3754968576 546000002908400001 661000001920700001
648000000145600034
And Backuptype and Devicetype were both = 2.
Thanks
Roy Harvey wrote:[vbcol=seagreen]
> What do you get back when you run these:
> RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
> RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
> Roy Harvey
> Beacon Falls, CT
> On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
>|||I really can't explain it any more than you can. I can only assume
that Something Is Not As It Appears. The question is, what?
Whatever it is, expect more questions (as you have had from Tracy and
me) before you get any answers.
I am not suggesting any of the following, but they all have to be
tested and dismissed. I am sure I am missing some others.
- The log is really not 110MB, but multiple GB.
- The file is from backing up some other database.
- The file is from backup up this database at some time in the past.
I hope some else has more specific advice that this!
Roy
On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:

>When I ran the first command here is what I got ....
>Data file size = 1751777280
>Tran log size = 5437521920
>Please help me understand how could the tran log get that big when I
>have an alert setup and also how is it that it is only 100MB now. I do
>not have the auto shrink option turned on.
>On the second command, here is what I got, providing you the info that
>you would care
>BackupSize FirstLsn LastLsn
> CheckpointLsn
>3754968576 546000002908400001 661000001920700001
>648000000145600034
>And Backuptype and Devicetype were both = 2.
>Thanks
>|||Here is a question for you, Is there a way to find out if the
transaction log file size was shrunk manually after the transaction
log backups?
Roy Harvey wrote:[vbcol=seagreen]
> I really can't explain it any more than you can. I can only assume
> that Something Is Not As It Appears. The question is, what?
> Whatever it is, expect more questions (as you have had from Tracy and
> me) before you get any answers.
> I am not suggesting any of the following, but they all have to be
> tested and dismissed. I am sure I am missing some others.
> - The log is really not 110MB, but multiple GB.
> - The file is from backing up some other database.
> - The file is from backup up this database at some time in the past.
> I hope some else has more specific advice that this!
> Roy
> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>|||Not that I know of, but there is a LOT I don't know. I do know that
shrinking the transaction log doesn't usually take effect immediately,
as it only happens after the logging reaches the end and has to go
back to the beginning.
Roy
On 4 Aug 2006 13:26:46 -0700, "shub" <shubtech@.gmail.com> wrote:
[vbcol=seagreen]
>Here is a question for you, Is there a way to find out if the
>transaction log file size was shrunk manually after the transaction
>log backups?
>Roy Harvey wrote:|||Another thing that needs checking: is the log on multiple files.
Roy