Database in Full recovery mode, with NO logbackups but logshipping setup

Question

Database in Full recovery mode, with NO logbackups but logshipping setup

caz100

SSCommitted

Points: 1827
More actions
August 24, 2018 at 9:34 am

#378393

Hi
I have encountered an instance in full recovery mode with NO logbackups but logshipping setup to a standby server.
Just wondering what the risks are with this setup?
On the primary it says the database has never had a log backup, the tramsaction log file is huge in size but 99% free in space.
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

No strange risk here. no log shipping is taking place because no log backups. The secondary is sitting there. The primary will have issues at some point with space if there is an active workload, but some point might be next month or a decade from now. You'd have to calculate the log load taking place per day.

caz100 SSCommitted Points: 1827 More actions · Answer 2

Thanks Steve

Well the log backups are taking place as part of the log shipping process. So that will allow a point in time recover.

I just thought it would be best practice to also have another job also backing up the primary transaction log file if the DB is in full recovery mode. But perhaps that is over kill as log shipping is already setup.

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

caz100 - Friday, August 24, 2018 9:34 AM
Hi
I have encountered an instance in full recovery mode with NO logbackups but logshipping setup to a standby server.
Just wondering what the risks are with this setup?
On the primary it says the database has never had a log backup, the tramsaction log file is huge in size but 99% free in space.
thanks in advance

Doesn't sound right. What Recovery Model are the databases in?

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

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

caz100 - Sunday, August 26, 2018 10:09 AM
Thanks Steve
Well the log backups are taking place as part of the log shipping process. So that will allow a point in time recover.
I just thought it would be best practice to also have another job also backing up the primary transaction log file if the DB is in full recovery mode. But perhaps that is over kill as log shipping is already setup.

Then log backups are being taken and the first assessment as to log backups not being taken is in error. What did you use to make that first assessment?

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

caz100 SSCommitted Points: 1827 More actions · Answer 5

No separate transaction log backup job. I understand now you don't require this if log shipping is already setup, as a job is created as part of log shipping and that will suffice for point in time recovery. I am used to setting up full backup and log backup jobs when in full recovery mode prior to setting up log shipping.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

Correct. You could have set up log shipping with a backup and never had a backup taken, so log shipping would not work. If log shipping is working, those are the same logs you use for recovery in a DR situation.

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 7

You generally DON'T want more than one backup job for the same logs - that will just mess with your log chain and make restores a whole lot harder.

So, yeah, you should definitely use the log backups you already have, but you can make any number of copies of those files and put them anywhere you like to feel safe.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

caz100 SSCommitted Points: 1827 More actions · Answer 8

caz100

SSCommitted

Points: 1827

August 27, 2018 at 8:04 am

#2003434

Thanks Vegard