Log shipping with a monthly archival process

Question

We will be implementing log shipping in our production environment in the next weeks.

We perform a monthly "cleanup" of the database, whereby we set the database to the SIMPLE recovery model, copy records to an historic database, then delete from the original to free up space.

I know that changing the database recovery model to simple will break log shipping (because the LSN chain will be broken). In that case, I would have to restore a full backup on the secondary server and start again with the log shipping process. I don't want to do this.

I am an 'accidental DBA' and inherited this archiving process. The process is as simple as selecting rows from the live database, inserting them into an archive database, and then deleting them from the live database.

My concern is that if I keep the database in the full recovery model during the deletion, the transaction log might grow in such a way that it could cause space issues and/or make the secondary server fall considerably behind the primary.

What options do I have here? Any advice will be appreciated.

Answer 1

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support.

1. Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log shipping chain so time out your LSCopy, LSBackup, and LSRestore jobs around this time when this occurs -- then run your cleanup process on primary DB

2. Switch your primary DB back to full recovery model (TSQL with SQL Agent job step), grow your (or perhaps shrink) your primary log file back to the "usual" size (SQL Agent TSQL again)

3. Run (or TSQL script with SQL Agent job) a FULL backup of the primary DB to the "usual" full backup location

4. Restore the secondary DB with the FULL backup file as in #3 above (TSQL with SQL Agent job step)

Afterwards, as timed out with some testing, what you already know about the timing, etc. the LSBackup, LSCopy, and LSRestore jobs should start working again.

I don't remember exactly, but you may need to purge the LSBackup and LSCopy TRN files on both primary and secondary after step #1 above to ensure SQL doesn't try to apply the broken chain TRN files to the secondary DB.

This can be done and I've done it before. It may not be "best practice" but if there's a business need, that should be enough justification if it works, gets the job done, and allows you to put some automation around it.