Following error occurs when database which is attempted to be created is smaller than Model Database. It is must that all the databases are larger than Model database and 512KB.
Following code will create the error discussed in this post.
CREATE DATABASE Tests
ON
( NAME = 'Tests',
FILENAME = 'c:\tests.mdf',
SIZE = 512KB )
GO
Msg 1803, Level 16, State 1, Line 1
The CREATE DATABASE statement failed. The primary file must be at least 2 MB to accommodate a copy of the model database.
Fix/WorkAround/Solution :
Create database which is larger than Model database and 512KB. Size of the Model Database will be displayed in the error message. If database is smaller than 512 KB it will throw error which I have described here : SQL SERVER – FIX : ERROR Msg 5174 Each file size must be greater than or equal to 512 KB
Reference : Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hi For me to cerate a db in sql server 2005 it gives me the following error:
CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.
So, how do reduce the primary file size of model database from 3 mb to 2mb?
In Denali (SQL Server 2012), the error message appears like below
The CREATE DATABASE statement failed. The primary file must be at least 4 MB to accommodate a copy of the model database.
I believe it means the size of Model Db is now 4 mb in Denali.
Thanks
Manish
Create a database without specifying SIZE
This example creates a database named products2. The file prods2_dat becomes the primary file with a size equal to the size of the primary file in the model database. The transaction log file is created automatically and is 25 percent of the size of the primary file, or 512 KB, whichever is larger. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products2
ON
( NAME = prods2_dat,
FILENAME = ‘c:\program files\microsoft sql server\mssql\data\prods2.mdf’ )
GO
Cuong Viet,Thanks a bunch….was stuck with the same error
does this file size change with respect to machine and update installed? Coz in one win7 x64 machine it asked for a 4MB size and in another win7 x64 asked for 5MB of size.
I have the same problem.
on a system with win 7 x64 there is no problem but on another system with the same os, i have this error.
why is that happening?
This seems to be a little Microsoft quirk, with the mb value deoending on the OS version, x64, etc.
My message was for 5mb, simply changed the value of 3475k into 6000k and it worked.