My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping

Question

My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping

mjvisintin

Valued Member

Points: 63
More actions
September 17, 2020 at 4:21 pm

#3789211

My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping by throwing it out of synch.
No matter when I try to time it, it throws of the transaction numbering sequence.
Does anyone know a work-around for this?
Thanks,
Michael

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

mjvisintin wrote:

My Weekly Full Transaction Log Backup and Shrink Keeps Killing my Log Shipping by throwing it out of synch.
No matter when I try to time it, it throws of the transaction numbering sequence.
Does anyone know a work-around for this?
Thanks,
Michael

Yes... stop shrinking your log file. Rather, figure out what is causing the (apparently) unwanted growth and fix it. Shrinking the log file once a week is a totally useless effort because it's just going to grow again until you fix whatever is causing the unwanted growth.

--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)

mjvisintin Valued Member Points: 63 More actions · Answer 2

Okay,

Thanks,

I thought it was just growing because we are putting so much data in there and that involves a transaction.

So you are indicating that they would be committed and gone if something wasn't preventing that, such as an open transaction?

Michael

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

mjvisintin wrote:

Okay,
Thanks,
I thought it was just growing because we are putting so much data in there and that involves a transaction.
So you are indicating that they would be committed and gone if something wasn't preventing that, such as an open transaction?
Michael

Putting "so much data in there" may mean that you need to do transaction log backups more often. If you're only doing transaction log backups once per week, that's a huge mistake for more than one reason. Certainly, log file size is one of the reasons but the biggest reason is that if you're not taking log file backups at least once per hour, then you stand a chance of losing a heck of a lot of data if something ever goes wrong.

If you insist on taking "FULL" backups of the databases only once per week, then I recommend taking a DIF back once per night and transaction log backups at least once per hour (preferably more often but that depends on what the RPO (Recovery Point Objective) for you company is.

Are you, by any chance, also doing supposed "Best Practice" index maintenance?

And, please, absolutely no insult intended or implied but it sounds like you're kind'a new at this. With that in mind, it would help us help you better if we knew what your SQL Server experience level is so we know the best way to describe things. For example, are you even familiar with the terms RPO and RTO? Do you know what a DIF backup is? Do you know what a "Recovery Model" is in a database (which affects a whole lot of things including backups)?

--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)

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 4

Keep min mind, with non simple recovery models, transactions are only flagged overwritable during a LOG backup !

To keep your log file size under control, it may require more frequent log backups to be run.

So, producing 1 weekly log backup may not be suitable for your log file size, maybe even not for your backup/recovery/DRP plan !

Check incremental log backup at MS docs

Shrinking your log file does not break LSN sequence!

Double check you have ALL produced log backup files and no other process is doing log backups !

In case you are using incremental log backup files, "Backup log ... with init" will overwrite the backup file !

That may cause the missing LSNs.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me