When you create a new database in SQL Server, you might assume that the system starts with an empty template and builds from scratch. In reality, every new database begins as a copy of model, one of the system databases. Understanding how model works, and how your changes affect downstream databases, is essential for anyone building, deploying, or maintaining SQL-based applications.
This article covers the 10 key concepts developers should understand to avoid surprises and to use the model database intentionally.
1. model Is the Template for Every New Database
Whenever you issue CREATE DATABASE, SQL Server copies the contents and settings of the model database.
That means:
- Every object in model is cloned into the new database.
- Every database-level setting in model becomes the new database’s default.
- File sizes and autogrowth settings carry forward as well.
If something appears in every new database unexpectedly, model is the first place to inspect.
2. Any Object You Add Will Propagate to New Databases
Developers sometimes add just a table or just a stored procedure to model for temporary testing.
This has consequences:
- That object will appear in every new database created afterward.
- Many teams discover rogue tables like
temp_table,backup_test, ormyprocyears later.
- Purging these objects from dozens of databases becomes painful.
Best practice: always avoid putting application objects or test code in model.
3. model Controls Default File Sizes and Autogrowth
Every new database inherits:
- Initial data and log file sizes
- Autogrowth settings (size vs percentage)
- The number of data files
- The location paths for data and log files (unless overridden)
If your organization has standard sizing rules, the model database is a critical place to enforce these defaults.
4. model Also Controls Key Database-Level Settings
Several important defaults come from model, such as:
- Recovery model (
FULLvsSIMPLE)
- ANSI settings
AUTO_SHRINK,AUTO_CLOSE, and similar options
- Default collation
Developers should understand that these defaults affect the behavior of new application databases until explicitly changed.
5. model Affects tempdb Creation at Startup
At SQL Server startup, tempdb is recreated from the model database (then altered with tempdb-specific settings).
This is why:
- Collation for tempdb can come from the model database.
- Some default configuration choices propagate to tempdb after each restart.
For performance tuning, understanding this relationship helps avoid unexpected tempdb behavior.
6. You Should Customize model – But Only Deliberately
There are legitimate reasons to modify the model database, including:
- Setting organization-wide defaults (recovery model, file sizes).
- Enforcing standard ANSI settings.
- Ensuring new databases start with predictable behavior.
- Adding required database configuration options.
- Adding organizational security code that needs to exist in every user database.
A customized model is a powerful governance tool, but only when done by design – not by accident.
7. You Should Not Put User Objects in model
Objects placed in model are nearly always mistakes unless there is a very specific, organizationally-approved reason.
Try to avoid putting any of these user objects in model:
- Tables
- Functions
- Stored procedures
- Users or logins
- Schemas
- Service broker objects
These will appear in every new database, including system-provisioned ones created by tools, installers, or automated deployment processes. However, there are some exceptions, like required organizational security code.
8. model Must Remain Clean and Operational
If model becomes corrupt, unavailable, or misconfigured:
- You cannot create new databases.
- tempdb cannot be created at startup.
- The SQL Server instance may fail to start.
For this reason:
- Avoid experimental changes in model.
- Treat model like any other critical system database.
- Include it in your system database backup strategy.
9. model Is Often Overlooked in Dev/Test Environments
Developers commonly work on machines where:
- The model database has been modified by past experiments.
- Autogrowth is set to tiny percentages.
- Recovery model is unintentionally
FULL.
- Compatibility level is wrong.
This leads to differences between dev, test, and production behavior.
Ensuring model is clean and standardized improves reproducibility across environments.
10. Review model After SQL Server Upgrades
Upgrades may adjust some settings, but old customizations often persist. After major upgrades, review that model still matches:
- Required defaults
- File paths for new storage
- Standard organizational settings
- Desired compatibility level
A quick check prevents legacy configurations from creeping into new databases.
Key Takeaways
Developers often overlook the model database, but it has a direct impact on every database they create. By understanding that model is a template, not an empty shell, you can:
- Avoid propagating accidental objects
- Maintain consistent database behavior
- Improve reliability across environments
- Ensure new databases start with the right defaults
A clean, well-configured model database is a small investment that pays off in consistency, predictability, and fewer surprises.
FAQs: Understanding the SQL Server model Database
1. What is model?
The template for every new database. SQL Server copies all objects and settings from model when creating a database.
2. Why do new databases have unexpected objects?
Because they exist in model. Anything placed there – even accidentally – replicates into every new database.
3. What gets inherited?
Objects, recovery model, ANSI settings, collation, file sizes, autogrowth rules, number of files, and file paths.
4. Should I add user objects to model?
No. User tables, procs, functions, or test items will spread everywhere.
5. When should I modify model?
Only to set intentional, organization-wide defaults (recovery model, file sizing, required security configuration).
6. How does model affect tempdb?
tempdb is recreated from model at startup, so settings like collation and some defaults flow into it.
7. What if model is corrupt or misconfigured?
SQL Server may fail to create new databases, recreate tempdb, or even start.
8. Why do dev/test environments differ?
Developers often have modified model databases, causing unintended differences in growth settings, defaults, or compatibility levels.
9. Does upgrading SQL Server affect model?
Some updates occur, but old customizations persist. Always review model after upgrades.
10. How do I maintain model?
Keep it clean, avoid user objects, back it up, and review after changes or upgrades.
Load comments