Post reply

Problems with Log Shipping: log backup size is larger than log file

  • February 16, 2015 at 9:17 am

    #294693

    We have a database with combined data size of close to 300 GB and log file size under 1 GB. It is log shipped, every 15 min we take log backup.

    The problem is that every 2nd log backup is constantly 5 GB, while others within normal range - between 5 and 200 MB. How it is possible to have a log backup larger than log file itself?

    Log shipping is performed by a scheduled job that calls exe file. We have already set database to simple, than back to full recovery model, took full backup again, and now log backups, but it did not help. Backups that are taken at XX:15 and XX:45 have 5 GB, but not strictly the same size, here are examples:

    02/15/2015 11:15 PM 5,080,282,112 DB_NAME__20150216041026.trn

    02/15/2015 11:30 PM 28,910,592 DB_NAME__20150216043002.trn

    02/15/2015 11:48 PM 5,049,372,160 DB_NAME__20150216044501.trn

    02/16/2015 12:00 AM 65,536 DB_NAME__20150216050001.trn

    02/16/2015 12:20 AM 5,057,185,280 DB_NAME__20150216051502.trn

    02/16/2015 12:30 AM 16,528,384 DB_NAME__20150216053001.trn

    02/16/2015 12:50 AM 5,065,424,896 DB_NAME__20150216054501.trn

    02/16/2015 01:00 AM 18,385,920 DB_NAME__20150216060002.trn

    02/16/2015 01:20 AM 5,061,656,576 DB_NAME__20150216061502.trn

    02/16/2015 01:30 AM 16,903,168 DB_NAME__20150216063002.trn

    02/16/2015 01:49 AM 5,033,098,240 DB_NAME__20150216064501.trn

    Thanks

  • February 16, 2015 at 2:18 pm

    #1777906

    Is the database getting set to the bulk-logged recovery model during this time? My guess is that the db is set to bulk-logged and then some maintenance is done that is minimally logged (index rebuilds) which causes the log backups to be larger than the transaction log file.

    From:

    https://technet.microsoft.com/en-us/library/ms190692(v=sql.100).aspx

    Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply