0

I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC).

It turned out that the aplication developer is setting the DB recovery method to 'Full', and the full backup nighlty routing does not purge and shrink that log file. In order to shrink it had to backup it up, change the recovery method to 'Simple', and backup with shrink.

I obviously want to prevent that from happening, so I am looking for options.

This is an MSSQL 2012 Express x64 instance.

The application developer is totally non-cooperative (external contractor, works against everyone) so I am assuming he wouldn't want me to leave the DB in 'simple' mode.

I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.

It seems to me that I should do this relatively often, as the larger the log file grows the more time it takes to backup and shrink, causing noticeable slowdowns.

If that routine is reasonable, and after each cycle I end up with 'a backup prior to purging the log' and 'a backup after the log was purged', and assuming both proceses exit without error, would it be wise to discard the first one? I would be keeping the 'data' backup and the 'purged log' backup.

I should add that AFAIK by doing "full backups" but not "regular log backups" I am sacrificing the possibility to revert to "states between two full backups", but I am not hindering the full backups themselves, as I have restored and cloned them many times in the past without issues. But if this is wrong please do let me know.

Note: For simplicity's sake let's say that -for now- being able to revert to a nightly full backup is good enough.

I hope I was able to explain myself. Thanks in advance.

Improve this question

1 Answer 1

Reset to default
1

I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.

Don't do this. It is a horrible idea because it gives anyone else looking at the system the wrong impression and is only a workaround to lie to the primary developer. That is not a good reason to make a technical decision around database recovery. There are unknown risks since we don't know anything about your company, application, etc. so only you can speak to those.

The correct path is to get a consensus among stakeholders, including any stubborn developers, and agree on a RTO/RPO that meets the business needs. Then adjust the recovery model accordingly.

Assuming you don't want to do that based on your post:

If you are 100% sure you don't care about recovery more granular than daily backups, keep it in full recovery mode and have a transaction log backup job that points to NUL so that growth is not an issue. It won't save the files anywhere, but will still allow you keep keep log growth under control.

Also see:

Full disclosure: expressdb.io is my site.

Improve this answer
3
  • Thank you for the insights. I should have mentioned that I wouldn't do that on the daily backups but on a weekly or bi-weekly schedule. but it isn't really something I'd like to do, hence why I'm asking for advice. Honest question: Would pointing to NUL be any less questionable than flipping the backup model on and off? I wll look into those links, much appreciated!. There has to be some elegant way to do this...
    – MadOp
    Aug 5, 2020 at 19:41
  • Its still bad, but you're not tying the log growth issue to the full backup schedule, something not entirely related in this case. I'd sleep more soundly knowing the transaction log was backed up every 15 minutes instead of hoping it didn't grow too large in between my weekly full backups.
    – LowlyDBA - John M
    Aug 5, 2020 at 20:02
  • While it will take me some time to read the links, establish an acceptable plan and achieve "my" solution, I am marking this as accepted solution given that just replying with useful data to a question like this can't go unappreciated. Thanks.
    – MadOp
    Aug 6, 2020 at 15:17

Your Answer

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

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