Primary File size error while creating database

Question

I am trying to create database in SQL Server 2005 and getting following error

Msg 1803, Level 16, State 1, Line 2

The CREATE DATABASE statement failed. The primary file must be at least 32 MB to accommodate a copy of the model database.

Create SQL

CREATE DATABASE [MyDatabase] ON  PRIMARY 
    ( NAME = N'MyDatabase_Data', FILENAME = N'D:\SQL SERVER\DataFiles\MyDatabase\MyDatabase.MDF' , SIZE = 3096KB , MAXSIZE = 29687808KB , FILEGROWTH = 262144KB ), 
    ( NAME = N'MyDatabase_Data2', FILENAME = N'D:\SQL SERVER\DataFiles\MyDatabase\MyDatabase_Data2.ndf' , SIZE = 3096KB , MAXSIZE = 10485760KB , FILEGROWTH = 262144KB ), 
     FILEGROUP [FG_Indexes] 
    ( NAME = N'MyDatabase_Indexes', FILENAME = N'D:\SQL SERVER\DataFiles\MyDatabase\MyDatabase_Indexes.ndf' , SIZE = 1920KB , MAXSIZE = 27136000KB , FILEGROWTH = 262144KB )
     LOG ON 
    ( NAME = N'MyDatabase_Log', FILENAME = N'D:\SQL SERVER\LogFiles\MyDatabase\MyDatabase_Log.LDF' , SIZE = 1920KB , MAXSIZE = 7883776KB , FILEGROWTH = 262144KB )

I checked msdb size and it is around 32MB…

EXEC sp_helpdb @dbname= 'MSDB'

MSDB size

How to overcome this error and create the database (without making any change to the create database script)?

Master DB

EXEC sp_helpdb @dbname= 'master'

Answer 1

When SQL Server creates a new database, it copies the model database as a template. model (depending on your version) has a specific initial size.

See this MSDN blog for details.

You are declaring your new database should have an initial size of 3MB:

CREATE DATABASE [MyDatabase] ON  PRIMARY 
    ( NAME = N'MyDatabase_Data', FILENAME = N'D:\SQL SERVER\DataFiles\MyDatabase\MyDatabase.MDF' , SIZE = 3096KB , MAXSIZE = 29687808KB , FILEGROWTH = 262144KB )

You need to increase the starting size to accommodate your model database' initial size.

NB: The default model is 3MB (on SQL Server 2012 - less on lower versions) -- you may want to look into why yours is 4MB.

Answer 2

All newly created databases use the Model database as a template. The new databases will start out no smaller than the Model database.
the error you are receiving indicates that the model database size is 32MB.
to fix it, create the database with a size larger than the model database size.
you can also shrink the model database to reduce its size.
Sample code to create database with custom size

CREATE DATABASE Tests
ON
( NAME = 'Tests',
FILENAME = 'c:\tests.mdf',
SIZE = 33000KB )
GO