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.