Bulk Logged Log Shipping and oversized log backups

eaglesoft

Hi

We are using the bulk-logged recovery model on a fairly large database (200GB). When performing database maintenance, the database log file size becomes approx 1.7GB. However, the backup created by SQL Backup is of a 60GB log (compressed to 9.7GB). Can anyone explain the reason for this and where the 60Gb comes from?

Regards
Vince Cullip

Robert C

Hi,

When you're in the bulk-logged model, I think I'm right in saying that a log backup contains not only the transaction log entries that have occurred since the last log backup, but also any data pages that have been modified by bulk-logged operations.

This is necessary for log shipping to work - the point of bulk-logged is to allow some operations to happen without logging all the information needed to reconstruct them in the transaction log. Therefore, in order to be able to restore from such a system, you also need to back up that extra information (as determined by the BCM - bulk change map(s)).

In your case, I suspect you're changing ~58GB of data pages between backups, which accounts for the majority of the large transaction log.

Hope that helps,
Robert

eaglesoft

Thanks Robert. I believe you are right. I'm going to try switching back to full and check the sizes there.

One problem I appear to have run into is that, since trying to ship the large log/pages, the target database has gone into error and will no longer accept restores. I'm getting

SQL error 823: I/O error (bad page ID) detected during read at offset 0x000000f90e000 in file 'E:\MSSQL\Data\UKREPDIWPRD_Data.mdf'

It might be related to a failed log restore, but I can't seem to get round it. I'm going to re-initialise the log shipping and start over.

Thanks
Vince