THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Do you perform log backup for the model database?

Are you stupid, you might think... But stop and think for a while. Model is no different from other databases. And by default it is in full recovery model. So as soon as you do your first database backup (you do backup your system databases, right?) the log for model will start filling up and autogrow. "But, hey, I don't do any modifications in model!", you probably say now. Fair, but other things happens in each database from time to time. Bottom line is that ldf file for model will start growing after a while . Perhaps not huge, but I find it "un-neat" to have a model with 3 MB mdf file and 20 MB ldf file.

Personally I prefer to have model in simple recovery since I feel that is a better default recovey model. An alternative is to regurarly set model in simple recovery and back to full recovery (schduled job).

Published Wednesday, November 18, 2009 2:05 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

dmmaxwell said:

I agree that SIMPLE is the better default recovery model.  It's part of my installation checklist to set Model to SIMPLE.  

However, I'm not clear on the reasoning behind having a scheduled job to switch recovery models.  What's the goal of that?  Isn't it just breaking the log chain?

November 18, 2009 8:52 AM
 

TiborKaraszi said:

<<However, I'm not clear on the reasoning behind having a scheduled job to switch recovery models.  What's the goal of that?  Isn't it just breaking the log chain?>>

That was referring to the model database. Say you don't want to have model in simple, for some reason. Then how do you keep the ldf flr model to keep growing? The reason for model in full is to have that as defaul (again, I don't do that, but other might want that) - not to have an unbroken chain of log backups to restore for model. I.e., we don't do log backup for model, but if we want to have it in full we want something to keep the ldf small.

November 18, 2009 11:40 AM
 

Artem Ervits said:

I thought I was the only one that felt this way. I always set Model db to backup the transaction log. At one point I've asked Tara Kizer regarding this and she replied that there maybe a problem causing this and I should look into that. Well, changing the recovery mode to Simple is definitely a solution, I prefer leaving that out and just setting up a trx backup. Either way works. Thanks for talking about this!

November 18, 2009 11:44 AM
 

Alex Rosa said:

BACKUP LOG from Model database or set recovery model to SIMPLE...I don't think so...

I have some concerns about that, because the model database isn't for user activities.

So, the model log file won't grow up.

If you are using the model db for user activities, you have a problem bigger than log file space usage.

Don't you think?

November 18, 2009 7:39 PM
 

Kalen Delaney said:

User activities are not the only thing that makes a log grow. Anything that changes the database in any way will be logged, so that could be system activities. It can grow, and as Tibor says, it won't grow really quickly usually, but it WILL grow. To prevent this, you need to either regularly back up model's log, or switch it to SIMPLE recovery.

~Kalen

November 18, 2009 8:49 PM
 

TiborKaraszi said:

Alex,

LOL... no, I'm not doing user activities in model. Did you ever look st the ldf file for model for a system which has been in production for a few years (assumnig regular db backups are done for model)? Then you would have noticed it is larger than default. As Kalen mentioned, system actcities occurs and over time it adds up.

November 19, 2009 3:26 AM
 

ALZDBA said:

- we only use model as it is by default. No procs, grants , users, ... defined by ourselves. No maintenance is configured for model db.

- I always keep a spare set of model db files ;)

- on my oldest sql2k (80pg mdf, 64pg ldf i.e. 0.625MB and 0.5MB)

- on my oldest sql2k5 production instance after 3 years it is still 152pg (mdf) and 64pg (ldf), i.e. 1.187MB and 0.50MB.

Off course performing a regular backup according to its recovery model is a best practice.

November 19, 2009 6:53 AM
 

TiborKaraszi said:

ALZDBA,

Are you doing regular db backup for model? If not, then it is likely that it is in auto-truncate for the log and it act as if in simple recovery model. I.e., that would be the reson for the ldf to not grow.

November 19, 2009 7:30 AM
 

Ewan said:

It's not a bad idea to set the model database to Simple Recovery - but more validly for the reasons that pop up occasionally on the forums - ie many people don't understand the Full Recovery model, so make the default installation the most 'basic' and least prone to misdeployment.

I have installations that are positively ancient (well, 5 years old anyway and older actually for SQL7), all are in Full recovery, and no log files bigger than 1MB... 20MB I would be unhappy with, like you.

I don't do backups or maintenance of model though, since I use a standard build I can always restore the database across instances. Tibor, what activity generates the log growth that you see? Any examples?

Do Service Packs modify model and truncate the log do you think?

Ewan

November 19, 2009 9:22 AM
 

Burt said:

I think most important is to have a backup process and follow it.  If you have model in full recovery mode then logs should be backed up.  If it's in simple recovery model, then obviously you can't.   I've got one customer with hundreds of databases in simple recovery mode.  He does this because he doesn't offer point in time recovery, therefore having model in simple recovery mode makes sense.  

November 19, 2009 10:26 AM
 

TiborKaraszi said:

Ewan,

I absolutely agree that default recovery for model should be simple. Every time I go to a new customer, I start inventory of the databases and there are always a bunch in full recovery but no log backups - huge ldf files... To be honest, I can't say for sure exactly what system events cause log records in model. The one I can think of is checkpoint - but I can't say whether the checkpoint can account for some few MBs over a year or two.

November 19, 2009 2:34 PM
 

Ewan said:

Yeah, we have a standard build script that detects if a new server is non-prod and if so, sets the model recovery to simple. But in Production we leave it as Full. Though I'll occasionally see a new database that one of my DBAs has created and left as Full Recovery, with no log backup defined. Cue slap upside the head.

November 20, 2009 8:48 AM
 

waren said:

I think you SQL2005 can use backup log

November 21, 2009 9:28 AM
 

Ewan said:

Interestingly, Bob Ward referred to this issue today on his blog

http://blogs.msdn.com/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

Basically, doing a regular Full Backup will cause model to grow. Which is why I don't see the problem in my environment.

Ewan

November 26, 2009 4:31 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement