August 24, 2018 at 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
August 24, 2018 at 10:03 am
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.
August 26, 2018 at 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.
August 26, 2018 at 10:12 am
caz100 - Friday, August 24, 2018 9:34 AMHiI 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
Change is inevitable... Change for the better is not.
August 26, 2018 at 10:14 am
caz100 - Sunday, August 26, 2018 10:09 AMThanks SteveWell 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
Change is inevitable... Change for the better is not.
August 26, 2018 at 10:20 am
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.
August 26, 2018 at 3:40 pm
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.
August 27, 2018 at 2:31 am
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.
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...)
August 27, 2018 at 8:04 am
Thanks Vegard
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply