CODEWITHSUNDEEP
sql-server
LCJ
LCJ

Reputation: 22652

Primary File size error while creating database

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

enter image description here

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'

enter image description here

Upvotes: 9

Views: 21409

Answers (3)

jrdevdba
jrdevdba

Reputation: 153

Just to add one more option: Because the model database is a template for new databases, I was able to get around the "accommodate" error by excluding the SIZE, MAXSIZE, and FILEGROWTH parameters. Those values were obtained directly from the model database and the db was created accordingly.

I know that approach may not work in all cases, as when you want to explicitly include the size parameters. But for those who adjust the model database to their liking, a CREATE DATABASE statement without the SIZE, MAXSIZE, and FILEGROWTH will simply use the values from model and won't throw the error.

HTH

Upvotes: 1

Dave C
Dave C

Reputation: 7392

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.

Upvotes: 18

Sam
Sam

Reputation: 789

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

Upvotes: 6

Related Questions