One wrong setting in the model database can spread to every database created afterward. Its recovery model, many options, and user objects form a template. File behavior has an important exception: do not assume model's every file size or autogrowth setting is copied unchanged.
Understand What the model Database Copies
When CREATE DATABASE runs, SQL Server copies model's contents and many options into the new database. User tables, stored procedures, permissions, and other objects placed in model can appear in everything created later. The recovery model is inherited. Some properties are intentionally not copied; Microsoft documents file properties as an exception, except the initial size of the data file. Explicit CREATE DATABASE clauses can also override defaults.
I keep model minimal because a convenient helper table there becomes a permanent object everywhere afterward. What is present today, and which future databases truly need it? A template change is an instance-wide policy change for future creations, not a local cleanup.
Inspect Recovery and Options in the model Database
Query the catalog for model's recovery setting, page verification, statistics settings, and other options the organization standardizes. Compare the result with the expected new-database policy. A full-recovery default without immediate backup and log-backup scheduling can let a new database's log grow unchecked.
SELECT name, recovery_model_desc,
page_verify_option_desc,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = N'model';Check current settings on the actual instance; defaults vary by version and edition. If changing recovery model, document the intended backup policy. A new database in FULL needs a data backup to establish a log chain, then regular log backups. The model setting does not create those jobs automatically.
Find Objects Added to model
List user objects and permissions in model. A custom object can be intentional, but it should have an owner, purpose, and test. Avoid keeping application-specific tables or credentials in this global template. The query below finds ordinary user objects; review database roles and permissions separately.
USE model;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,
name, type_desc, create_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY schema_name, name;An existing database does not receive later changes to model. Removing an unwanted object from model prevents future copies but does not remove it from copies already made. Inventory those separately before making any cleanup decision. Back up model before a deliberate template change.
Compare model Files With a Fresh Copy
sys.master_files shows model's data and log file sizes and growth settings. A growth value can be percent-based or fixed pages. SQL Server's documented copying rule does not promise that every file property propagates. Create a small throwaway database in a lab and inspect its files. On my test instance, a fresh copy matched model's 8 MB files and 64 MB growth. Those are also the engine defaults, so that match alone proves little. That observation is more useful than assuming model file growth became policy everywhere.
SELECT DB_NAME(database_id) AS database_name,
name, type_desc, size * 8.0 / 1024 AS size_mb,
CASE WHEN is_percent_growth = 1 THEN growth
ELSE growth * 8.0 / 1024 END AS growth_value,
is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'model');A fixed growth of a few megabytes can cause repeated small expansions. Percent growth becomes a larger and less predictable increment as the file grows. For production data and log files, choose a measured fixed increment and pre-size for the known workload. Make that setting explicit in the CREATE DATABASE deployment or immediately after creation, then verify it.
Fix Growth and Size Deliberately
If model itself is too small for its template work, change its primary file size and growth after a backup. For new application databases, configure both data and log files explicitly. Do not try to force an inheritance rule the engine does not provide. The example changes model's own file growth, then shows the separate check to run after each new CREATE.
ALTER DATABASE model
MODIFY FILE (NAME = N'modeldev', FILEGROWTH = 64MB);
ALTER DATABASE model
MODIFY FILE (NAME = N'modellog', FILEGROWTH = 64MB);
SELECT name, type_desc, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'model');Replace logical names after inspecting the instance. A production growth increment can be larger than this example. Do not shrink model merely to reach a target number; check the amount of data it copies and the system's actual file settings. Use a reviewed deployment for any change to a system database.
Watch the model Log in FULL Recovery
If model uses FULL recovery, its own log also needs an operating policy. Check log space and recent backups. The log of a new database can grow if its FULL recovery is inherited but the backup scheduler does not discover it. That is the more common fleet-wide consequence. Include new-database enrollment in data and log backup jobs as part of CREATE DATABASE.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name IN (N'model', N'NewApplicationDb');I create a lab database after each template change and verify its recovery setting, objects, data-file initial size, log size, and autogrowth. That test documents what the current build actually does and catches a script that overrides the template. The outcome is a controlled default, not a collection of assumptions about file settings.
Test the Actual CREATE Path
An application can create a database with explicit SIZE, FILEGROWTH, RECOVERY, and options after the initial CREATE. A template audit alone cannot tell what that application's final result will look like. Rehearse its exact provisioning script in a lab, inspect sys.databases and sys.master_files afterward, and compare with the standard. Keep the result as a deployment test.
I include a negative case: provision a database without the post-create growth step and see whether the validation catches it. A standard that lives only in a document will drift. A standard checked by a query after every CREATE is enforceable.
Handle Existing Databases Separately
Changing model today affects future creations, not the hundreds already present. Inventory recovery settings, file growth, page verification, and statistics settings across existing databases as a separate task. Apply fixes only with the owner and backup plan for each database. Converting a production database to FULL without enrolling it in log backups can worsen capacity risk. Changing file growth can cause a long expansion at the next busy moment if the files are undersized.
Protect the model Database as a System Template
Because model is required for every CREATE and influences tempdb creation, an accidental incompatible change can have instance-wide effects. Back it up before adding objects or changing options, and test a restart and new database creation on a nonproduction instance. Keep the template as small as possible. A helper object that belongs with DBA utilities should not be planted in model merely to avoid a setup step.
The log of model itself deserves monitoring if it grows, but distinguish that from the growing logs of newly created copies. Measure which file is growing before changing a recovery model or backup schedule.
Related reading on this blog: FIX: Error 1807 Could not obtain exclusive lock on database 'model'. Retry the operation later: Part 2 and Simple Recovery Model and Restrictions.
Model is not a promise about every file property, it is a template whose result must be checked.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
