template-database-size.md

Template database size

When doing a create database that new database is created based on the model database. See The model Database and Creating New Databases. When defining a size for a new database, that size is ignored if it is smaller than the model database size:

The size specified for the primary file must be at least as large as the primary file of the model database.

Since the model database is 8MB, the default (and smallest) size for any new database is also 8MB. This is not ideal when using LocalDB for unit tests, since a database is created for each test, is means an 8MB file needs to be created for each test, with the resulting cost in IO time and disk usage.

To have a smaller file size DBCC SHRINKFILE is performed on the model database at the time a new SqlLocalDB instance is created. The smallest size allowed is 3MB.

use model;
dbcc shrinkfile(modeldev, {size})

^{snippet source / anchor}

Override

The SqlInstance constructor has a templateSize parameter to override the default size. Increasing this value, usually to an average of the resultant database sizes, can improve performance by reducing the number of time SQL Server needs to resize the database.