Results 1 to 7 of 7

Thread: increased log backup size

  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Question increased log backup size

    Hi,

    log backup of a database has been scheduled at every 30 minutes. Backup size is between 1-2 GB as compared to 20-30 MB earlier. The recovery model of the database is Bulk Logged and Log file is always 95-96 % free between the backups.

    How can i find which transaction is responsible for increased backups size when Profiler is not an option?

  2. #2
    Join Date
    Apr 2011
    Location
    Dallas, TX
    Posts
    60
    There is an option in the backup to create a new file or append/add to an existing file. Sounds like you're adding to...

  3. #3
    Join Date
    Sep 2013
    Posts
    4
    no, a new file is created every 30 minutes. That is why it is a concern that what actually is being done on the database that log backup is in GB.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Does db have many bulk load transactions? If so, log size will be small but log backup maybe big because sql will backup all bulk load related extents during log backup.

  5. #5
    Join Date
    Sep 2013
    Posts
    4
    yes true log backups is of large size in bulk logged recovery model however, I am not doing any bulk logged transactions and same has been checked with the concerned apps team too and still log backups are of large size and every 30 minutes.

    I tried to find out by checking active transactions between the backups however couldn't succeeded.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Bulk logged transactions are not limited to bulk load, reindex is one for instance.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,926
    Check if there are any open transactions. Transaction log backup may not be truncating after each backup due to open tran.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •