Prev: ALTER TABLE and ANSI_WARNINGS
Next: Deploy a DDL trigger to prevent dropping tables for mulitple datab
From: Mark on 5 May 2010 13:05
My transaction log has a fixed size of 2 GB. Every once in a while our
transaction log .trn backup files can be 15 GB or larger. Prior to recent
weeks, I had assumed that the .trn file would never be larger than the actual
..ldf log file. I guess the fact that it is happening must be normal - but
could someone explain this?

Thanks in advance.
-Mark
From: Tom Cooper on 5 May 2010 13:29
If you use the bulk-logged recovery model and do anything that is minimally
logged, what SQL Server does is just log the extents that are changed (that
is, just which extents, NOT the contents of each extent). This allows the
logging to be very fast, and not use much space in the ldf file. However,
when you do a log backup, the backup needs the contents of the extent(s)
that were updated, not just which extent(s) they were (otherwise if you ever
had to restore the log backup there would be no way to recover those
extents). So as part of the log backup, SQL also copies the contents of
each of those extents. Which can make the .trn file much larger than the
..ldf file.

Tom

"Mark" <Mark(a)discussions.microsoft.com> wrote in message
news:AE91D5C1-D28A-4D18-9800-B6E03CE357B7(a)microsoft.com...
> My transaction log has a fixed size of 2 GB. Every once in a while our
> transaction log .trn backup files can be 15 GB or larger. Prior to recent
> weeks, I had assumed that the .trn file would never be larger than the
> actual
> .ldf log file. I guess the fact that it is happening must be normal - but
> could someone explain this?
>
> Thanks in advance.
> -Mark

From: Mark on 5 May 2010 14:12
Fascinating. In this case we are using Full recovery mode. Does that change
your answer? Thanks again.
From: Chris Wood on 5 May 2010 17:01
Mark,

Check your log backup job. Is it appending to a file or creating a new log
backup file each time?

Chris

"Mark" <Mark(a)discussions.microsoft.com> wrote in message
news:38D299BC-47F3-442E-80CF-38800FCED45D(a)microsoft.com...
> Fascinating. In this case we are using Full recovery mode. Does that
> change
> your answer? Thanks again.


From: Tom Cooper on 5 May 2010 17:25
Chris' suggestion is possible, you can get a big file because you keep
appending multiple backups into the same file. Fortunately, it's easy to
test both my suggestion and Chris' at the same time. Get one of your large
(15 gig) backups and do a RESTORE HEADERONLY on that file.

RESTORE HEADERONLY does not restore the backup, it just reads it and returns
one row for each backupset in that file. So you can see if Chris'
suggestion is correct because then there will be a number of rows returned
because the file contains more than one backup. If my suggestion is
correct, there will be one row, but the value in the HasBulkLoggedData
column will be set to 1. If that is set to 1, then you are doing bulk
logged operations. My guess would be since this only happens to you "every
once in a while", that you have some job that runs occasionally that puts
the database in bulk logged recovery mode, does some operations, and then
puts it back in Full recovery mode.

For more info on how to run RESTORE HEADERONLY and the date it returns, see
BOL.

Tom

"Mark" <Mark(a)discussions.microsoft.com> wrote in message
news:38D299BC-47F3-442E-80CF-38800FCED45D(a)microsoft.com...
> Fascinating. In this case we are using Full recovery mode. Does that
> change
> your answer? Thanks again.

 |  Next  |  Last
Pages: 1 2
Prev: ALTER TABLE and ANSI_WARNINGS
Next: Deploy a DDL trigger to prevent dropping tables for mulitple datab