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

  • 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

  • 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.

  • 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.

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

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

  • 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.

  • 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.

  • 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)
  • Thanks Vegard

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

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