Simple recovery mode with mirroring - is that possible?

by Darth Veyda   Last Updated May 05, 2017 05:06 AM

(The usual 'I'm not a real DBA, but I need to handle this somehow' disclamer here)

We have a database with Full recovery model that doesn't really need a Full recovery model... only it does.

Some context first: the database in question contains some data used by the nightly job scheduler (e.g. server connection parameters, queue state etc.). This data doesn't really warrant for the point-in-time recovery or even, for that matter, for backups, because it's much easier to re-create the whole database from scratch. At the same time it's a production database with mirroring enabled, so it can stay online and available if we switch to DR server for any reason.

So we are in a Catch-22 situation: either we switch to Simple and lose the automatic failover setup (which is kind of necessary for us to have), or we keep Full and take regular log backups that we don't actually need, otherwise transaction log file will keep growing (with data that, again, we don't actually need).

The only solution I could come up with is scheduling an automatic job that discards all transaction log data, but I've been wondering if there is a saner way to handle this.

Related Questions

What is the role of eager writer?

Updated March 12, 2016 07:02 AM

Is Recovery Model Type Effect Rapid growth For DB?

Updated September 22, 2017 13:06 PM