SQL SERVER – FIX : ERROR Msg 1803 The CREATE DATABASE statement failed. The primary file must be at least 2 MB to accommodate a copy of the model database

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 (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – FIX : ERROR Msg 1803 The CREATE DATABASE statement failed. The primary file must be at least 2 MB to accommodate a copy of the model database

  1. 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?

  2. 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

  3. 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

  4. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s