Log backup file is very large

Question

Log backup file is very large

dxu

SSC-Addicted

Points: 483
More actions
October 10, 2021 at 9:44 pm

#3939287

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

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

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Michael L John One Orange Chip Points: 27056 More actions · Answer 2

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/

dxu SSC-Addicted Points: 483 More actions · Answer 3

Michael L John wrote:

Haw large are the differentials?

It is less than 2 G.

dxu SSC-Addicted Points: 483 More actions · Answer 4

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

dxu SSC-Addicted Points: 483 More actions · Answer 5

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

dxu SSC-Addicted Points: 483 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

Michael L John One Orange Chip Points: 27056 More actions · Answer 8

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/

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

dxu SSC-Addicted Points: 483 More actions · Answer 10

dxu

SSC-Addicted

Points: 483

October 12, 2021 at 10:16 pm

#3939951

Thank you all guys!

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 11

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning