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 |