Missing step in backup (DR) plan Unfortunately, I am assuming like most folks in this role, the title DBA came by default rather than design.

I am trying to manage our farm of 5 SQL Server 2016 servers, each having a warm stand-by for our Disaster Recovery.

We have queried our user base and have come to the decision that some downtown is acceptable as long as we can minimize data loss.  We do not need point in time recoveries, only to ensure that we have the most current data possible based on the users data loss acceptability. 

What we have decide for our model is each database on each of the primaries will be backed up and restored on the appropriate stand-by (with the database left in Stand-By/Read Only mode) and then a log shipping job created to ship the log to the standby server every 30 seconds to 1 hour depending on the data loss sensitivity.  Then, on each stand by server, once every 15 minutes to 1 hour, a restore job runs to restore all the transactions shipped from the primary into the stand by database.  Once the transactions have been shipped to the stand by and restored successfully, I no longer care about those transactions.

This works great as I can query the stand by databases and see the updated data as expected.

Where I am lacking information in is, my transaction log is growing larger than my DB file on my primary.  I ASSUMED that the log shipping would mark the log as backed up, but I am apparently wrong.

I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.

I have queried the internet for answers, but I get about as many different answers as there are results, so I am turning here for advise.

Thanks in advance,
Keith
I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.


This is a misconception/myth. A fullbackup will never break the log backup chain. Only an adhoc NON COPY_ONLY backup will break the log backup chain.

In full recovery, only a full backup will truncate the log.


  ran a log_reuse_wait_desc and there is nothing holding the log


Check your autogrowth settings. Make sure it is not set to Percent autogrowth and the MB autogrowth is sensible.

You can check my script to check.

Also, you should check : Why Does the Transaction Log Keep Growing or Run Out of Space?