Log backup file is very large

  • Hi,

    We have got an issue with the SQL database backup. The log file itself is relatively small which is around 5 G. But for some reason the hourly log backup file is very big. The size is between 20G to 120G. In particular the one in 1PM.  I hardly believe the amount of tractions are so much different.

    We backup like below:

    Full backup 1AM/Differential backup 7AM and 1PM/ Hourly log backup every hour.

    Thanks

     

  • Are you initializing the backup file each time, or is the backup file stacking? Try running RESTORE HEADERONLY to see if you're putting multiple backups into a single file. That's what this sounds like. Unless you're running shrinks along with your log backups (please stop if you are), it's not really possible to have a 5gb log, but a 50gb backup of that log. It's not Snoopy's dog house. Or, for younger (and older) audiences, the TARDIS.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Haw large are the differentials?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    Haw large are the differentials?

    It is less than 2 G.

  • Grant Fritchey wrote:

    Are you initializing the backup file each time, or is the backup file stacking? Try running RESTORE HEADERONLY to see if you're putting multiple backups into a single file. That's what this sounds like. Unless you're running shrinks along with your log backups (please stop if you are), it's not really possible to have a 5gb log, but a 50gb backup of that log. It's not Snoopy's dog house. Or, for younger (and older) audiences, the TARDIS.

    Hi Grant, thanks for your reply.  Yes I think you are correct. Below is the result when I query one of the backup file. It seems stack all the back up in one file. How can I fix this? I also past my script here. Can you please help? Thanks

    2021_10_12_09_21_29_SQLQuery197.sql_DBMEL02.ClaytonsSQL_CLAYTONSKITCHEN_dxu_86_Microsoft_SQ

    declare @LogName as varchar(255)

    declare @LogNameTemp as varchar(255)

    declare @fileNameToDelete as varchar(255)

    DECLARE @SQlTemp as varchar(255)

    select @LogName=case

    when DATEPART(hour,GETDATE()) > 12

    then cast((DATEPART(hour,GETDATE()) - 12) as varCHAR(2)) + 'PM.bak'

    else cast(DATEPART(hour,GETDATE()) as varCHAR(2)) + 'AM.bak'

    end

    Select @LogNameTemp =@LogName

    SET @fileNameToDelete='A:\Backups\ClaytonsSQL_Log_'+ CONVERT(VARCHAR(20),GETDATE()-1,112)+@LogName

    SELECT @LogName='A:\Backups\ClaytonsSQL_Log_'+ @LogName

    select @SQlTemp='del ' + @fileNameToDelete

    BACKUP LOG [ClaytonsSQL] TO DISK = @LogName WITH NOFORMAT, NOINIT,

    NAME = N'ClaytonsSQL-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    SELECT @LogName='A:\Backups\ClaytonsSupport_Log_' + @LogNameTemp

    BACKUP LOG [ClaytonsSupport] TO DISK = @LogName WITH NOFORMAT, NOINIT,

    NAME = N'ClaytonsSupport-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    SET @fileNameToDelete='A:\Backups\ClaytonsSupport_Log_'+ CONVERT(VARCHAR(20),GETDATE()-1,112)+@LogNameTemp

    select @SQlTemp='del ' + @fileNameToDelete

     

  • Grant Fritchey wrote:

    Are you initializing the backup file each time, or is the backup file stacking? Try running RESTORE HEADERONLY to see if you're putting multiple backups into a single file. That's what this sounds like. Unless you're running shrinks along with your log backups (please stop if you are), it's not really possible to have a 5gb log, but a 50gb backup of that log. It's not Snoopy's dog house. Or, for younger (and older) audiences, the TARDIS.

    Ok.  I have got it. Thanks

  • Sorry guys another question. What is the benefit to append backups to one file? If I do full backup/diff backup/log backup every day, why I need to append files?

    Thanks

  • The only reason I can see for doing such a thing is to have all the files since the last backup in one file especially for when it comes to  doing "rolling deletes" of backup files. I don't agree with that but that's the only reason I can think of.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • dxu wrote:

    Sorry guys another question. What is the benefit to append backups to one file? If I do full backup/diff backup/log backup every day, why I need to append files?

    Thanks

    It's a throwback to the original days of SQL Server / Sybase I think.

    You had to create a device for backups, and backup to a device.  The device was named "Full_Backup_MMDDYYYY" or whatever, and the backups accumulated on the device.

    And, if I remember correctly, when you backed up directly to tape (yes, that was a thing) it was far faster to write to the same file in a device for a week or so as opposed to new files with each backup.

    Now, like Jeff, I can't really think of a good reason to do this.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    dxu wrote:

    Sorry guys another question. What is the benefit to append backups to one file? If I do full backup/diff backup/log backup every day, why I need to append files?

    Thanks

    It's a throwback to the original days of SQL Server / Sybase I think.

    You had to create a device for backups, and backup to a device.  The device was named "Full_Backup_MMDDYYYY" or whatever, and the backups accumulated on the device.

    And, if I remember correctly, when you backed up directly to tape (yes, that was a thing) it was far faster to write to the same file in a device for a week or so as opposed to new files with each backup.

    Now, like Jeff, I can't really think of a good reason to do this.

    But I can think of several really bad things that can happen if you do this.  The most obvious is using a single file for both backups and logs and initializing the file for the backup.  The minute you initialize the file - all previous backups are deleted, wiping out any way of recovering from the previous backup and applying transaction log backups from that point forward.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you all guys!

  • Sorry. I was away. Looks like everyone took good care of you. Glad you tracked down the issue.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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