Is model db important?

Few days ago I have been asked for help in process of installation a SCOM. Admins wasn’t been able to install some parts of this system because installation wizards reports some unexpected errors.

When I arrived to the SQL instance I found something interesting which ensured me that we should talk more about purpose of model database.

Please remember model is a template on which all newly created databases are made. User databases inherit properties of the model (for example recovery model), file size and file growth settings and also all objects created in the model (tables, functions, schemas, stored procedures, etc…).

Only if you explicitly specified settings in process of database creation will overwrite model default settings. So keep in mind if you left model database for example with full recovery model, every newly created database will require backup of transaction logs on regular basis.

Misunderstandings the concept of model db like this could lead (in case of recovery model=FULL) to unexpected growth of transaction log.

In my case I have got an instance with model db filled with user data (780MB in tables). It means every newly created database will have a copy of those data by default. Because model is a template database you know . So when the wizard tried to create database for SCOM sized to 500MB it will always fail because minimum size of new database must be greater or equal than model database.

So remember this concept and protect you and your SQL server from those issues in the future.