2

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

Improve this question
1
  • Mirroring cannot be configured when recovery model is simple.
    – Shanky
    Commented May 5, 2017 at 6:10

2 Answers 2

Reset to default
3

Mirroring uses the transaction log to redo everything at the mirror site. In simple recovery model, the transaction log is effectively transitory and only enough information is kept in the log for undo/redo to keep the database consistent during crash recovery. So Database mirroring is not possible in Simple Recovery model as it is not supported.

Improve this answer
2
  • I see, thank you vm for clarification. I suppose switching temporarily to Simple wouldn't work either without turning mirroring off, is that correct?
    – Darth Veyda
    Commented May 5, 2017 at 5:43
  • @DarthVeyda correct, and you wouldn't be able to just flip it back on after changing recovery models either.
    – Erik Darling
    Commented May 5, 2017 at 11:54
2

While I dont normally recommend this, for your specific situation this will work. You can take log backups to the NUL device. The NUL device is a device file in the operating system that discards all data written to it but reports that the write operation succeeded. This is the basic TSQL syntax (Note there is only 1 L in NUL for this device, not be confused with NULL):

Backup log [TestDatabase] to Disk = 'NUL'

You could put this into a SQL Agent job and schedule it accordingly. Just a note of caution though for anyone reading this, this will destroy 'Point-in-Time' recovery, not generally recommended.

Improve this answer
1
  • This is exactly what I ended up doing, after all - good to know I'm not the only one to think this is the only way in our case :) (Not marking this as the correct answer only to avoid encouraging people to do the same with their databases when they really shouldn't) It's a pity that SQL Server does not provide any means to have a failover setup without regular backups :(
    – Darth Veyda
    Commented May 9, 2017 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.