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?
Best Answer
I think you made a leap. If you change
modelto 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 inmodelthat would prevent that from happening). But, I don't know how you're usingmodel. 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 (usingCREATE DATABASEor 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.