2

I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full Recovery and would rather keep it that way but I was wondering what's the recommended way of handling this.

My instinct is to run a Log Backup on it but I also know the system databases are a different breed of creatures. Most places I've looked suggest switching it to Simple Recovery or just leave the matter at 'model is small and rarely changes, backing up the log is unnecessary'. But do they really mean the best practice is to let it autogrow constantly?

Improve this question

1 Answer 1

Reset to default
2

I think you made a leap. If you change model to simple recovery, the log won't autogrow constantly, because that allows the log space to be re-used (and it is unlikely there is anything going on in model that would prevent that from happening). But, I don't know how you're using model. If it's being used to store objects and data that you want created in every database, maybe FULL is a better idea, and taking log backups will allow you to restore to a point in time. FULL is also a good idea if you create databases manually (using CREATE DATABASE or the UI, without attaching), and you want all of them to be in full or simple recovery by default, then that should drive your decision, not some observation about log space used.

Improve this answer
2
  • Sorry, I don't think I explained that very well. FULL recovery is what I want but how do I properly maintain it? The assumption I keep running into is that model doesn't really grow so I shouldn't need log backups. Very slowly over the years though, mine has grown.
    – Script Wolf
    Commented Feb 9, 2016 at 21:16
  • There is obviously some activity against your model database, therefore, if you want the log size to remain stable, you're going to need to back up the log periodically (probably not as often as user databases with recovery point objectives). We can't tell you why your model database is generating log activity, or why people you run into assume that because their model database's log doesn't grow, yours shouldn't either. I've owned three Mercedes and not one of them has ever had a flat tire. Does that mean if you buy a Mercedes, you'll never have a flat tire either? :-)
    – Aaron Bertrand
    Commented Feb 9, 2016 at 21:24

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.