Recently I encountered error 1803 when working on SQL Server 2012. The script I ran against a SQL Server 2012 instance was

CREATE DATABASE [suspect_db] ON  PRIMARY

( NAME = N'suspect_db', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'suspect_db_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

 

I was confident I used this same script successfully on previous versions of SQL Server. Therefore, I examined the error message and the script properly:

Msg 1803, Level 16, State 1, Line 3

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

 

Then I went and compared the physical size of model and noticed that the size changed between SQL Server 2012 and previous versions. Here is a comparison of the sizes:

 

SQL Server version

Physical file size (bytes)

sp_spaceused information

model.mdf

modellog.ldf

reserved

data

index_size

unused

2000

655,360

524,288

528 KB

144 KB

280 KB

104 KB

2005

1,245,184

524,288

1136 KB

472 KB

560 KB

104 KB

2008

1,310,720

524,288

1200 KB

472 KB

624 KB

104 KB

2008 R2

1,310,720

524,288

1216 KB

512 KB

632 KB

72 KB

2012

2,162,688

524,288

2096 KB

792 KB

1080 KB

224 KB

 

So, the next obvious question is - Why this change now?

            When the SQL Server product team ships new features or enhancements to existing features, these involve changes to the core metadata. These might be in the form of new system tables, views, stored procedures and other objects. In some occasions, these changes may be server wide that you just make the change to the associated catalog tables in the msdb or master database. If this is a change that needs to be implemented in every database, then you will see the effect in the model database. Since we added several new exciting features in SQL Server 2012, we needed to add supporting system tables in every database. Therefore, those need to be persisted in model database first. Depending upon on the number of system tables you add and the available free space, the data file needs to grow. That is what happened in SQL 2012. New stored procedures and system wide views are normally implemented in the Resource database and installed as part of the setup.

 

If you have scripts where you specify the initial size of the database [especially SQL Express], make sure to consider this factor when migrating applications to SQL Server 2012.

 

Here is the query I used to find out what system tables were added in each version of SQL Server:

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.hobt_id)

where au.type in ( 1 , 3 )

union

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.partition_id)

where au.type in ( 2 )

 

The output I got from different versions is shown below with the changes highlighted. As you can see these system tables correspond to the new features introduced in SQL Server 2012 [e.g. AlwaysOn, Contained database, FileTable].

 

SQL 2012

SQL 2008 R2

object_name

index_id

rows

total_pages

size_bytes

object_name

index_id

rows

total_pages

size_bytes

filestream_tombstone_2073058421

1

0

0

0

filestream_tombstone_2073058421

1

0

0

0

filestream_tombstone_2073058421

2

0

0

0

filestream_tombstone_2073058421

2

0

0

0

filetable_updates_2105058535

1

0

0

0

 

 

 

 

 

queue_messages_1977058079

1

0

0

0

queue_messages_1977058079

1

0

0

0

queue_messages_1977058079

2

0

0

0

queue_messages_1977058079

2

0

0

0

queue_messages_2009058193

1

0

0

0

queue_messages_2009058193

1

0

0

0

queue_messages_2009058193

2

0

0

0

queue_messages_2009058193

2

0

0

0

queue_messages_2041058307

1

0

0

0

queue_messages_2041058307

1

0

0

0

queue_messages_2041058307

2

0

0

0

queue_messages_2041058307

2

0

0

0

sysallocunits

1

138

4

32,768

sysallocunits

1

103

4

32,768

sysallocunits

2

138

2

16,384

sysallocunits

2

103

2

16,384

sysasymkeys

1

0

0

0

sysasymkeys

1

0

0

0

sysasymkeys

2

0

0

0

sysasymkeys

2

0

0

0

sysasymkeys

3

0

0

0

sysasymkeys

3

0

0

0

sysaudacts

1

0

0

0

sysaudacts

1

0

0

0

sysbinobjs

1

23

2

16,384

sysbinobjs

1

23

2

16,384

sysbinobjs

2

23

2

16,384

sysbinobjs

2

23

2

16,384

sysbinsubobjs

1

3

2

16,384

sysbinsubobjs

1

3

2

16,384

sysbinsubobjs

2

3

2

16,384

sysbinsubobjs

2

3

2

16,384

sysbrickfiles

1

0

0

0

 

 

 

 

 

syscerts

1

0

0

0

syscerts

1

0

0

0

syscerts

2

0

0

0

syscerts

2

0

0

0

syscerts

3

0

0

0

syscerts

3

0

0

0

syscerts

4

0

0

0

syscerts

4

0

0

0

syschildinsts

1

0

0

0

 

 

 

 

 

sysclones

1

0

0

0

 

 

 

 

 

sysclsobjs

1

16

2

16,384

sysclsobjs

1

16

2

16,384

sysclsobjs

2

16

2

16,384

sysclsobjs

2

16

2

16,384

syscolpars

1

694

17

139,264

syscolpars

1

483

16

131,072

syscolpars

2

694

7

57,344

syscolpars

2

483

5

40,960

syscommittab

1

0

0

0

syscommittab

1

0

0

0

syscommittab

2

0

0

0

syscommittab

2

0

0

0

syscompfragments

1

0

0

0

syscompfragments

1

0

0

0

sysconvgroup

1

0

0

0

sysconvgroup

1

0

0

0

syscscolsegments

1

0

0

0

 

 

 

 

 

syscsdictionaries

1

0

0

0

 

 

 

 

 

sysdbfiles

1

2

2

16,384

 

 

 

 

 

sysdbfrag

1

0

0

0

 

 

 

 

 

sysdbfrag

2

0

0

0

 

 

 

 

 

sysdbreg

1

0

0

0

 

 

 

 

 

sysdbreg

2

0

0

0

 

 

 

 

 

sysdbreg

3

0

0

0

 

 

 

 

 

sysdercv

1

0

0

0

sysdercv

1

0

0

0

sysdesend

1

0

0

0

sysdesend

1

0

0

0

sysendpts

1

0

0

0

 

 

 

 

 

sysendpts

2

0

0

0

 

 

 

 

 

sysfgfrag

1

0

0

0

sysfgfrag

1

2

2

16,384

sysfiles1

0

2

2

16,384

sysfiles1

0

2

2

16,384

sysfoqueues

1

0

0

0

 

 

 

 

 

sysfos

1

0

0

0

 

 

 

 

 

sysfos

2

0

0

0

 

 

 

 

 

sysftinds

1

0

0

0

sysftinds

1

0

0

0

sysftproperties

1

0

0

0

 

 

 

 

 

sysftproperties

2

0

0

0

 

 

 

 

 

sysftproperties

3

0

0

0

 

 

 

 

 

sysftsemanticsdb

1

0

0

0

 

 

 

 

 

sysftstops

1

0

0

0

sysftstops

1

0

0

0

sysguidrefs

1

0

0

0

sysguidrefs

1

0

0

0

sysguidrefs

2

0

0

0

sysguidrefs

2

0

0

0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

sysidxstats

1

180

4

32,768

sysidxstats

1

123

4

32,768

sysidxstats

2

180

2

16,384

sysidxstats

2

123

2

16,384

sysiscols

1

367

4

32,768

sysiscols

1

275

4

32,768

sysiscols

2

367

2

16,384

sysiscols

2

275

2

16,384

syslnklgns

1

0

0

0

 

 

 

 

 

sysmultiobjrefs

1

107

2

16,384

sysmultiobjrefs

1

106

2

16,384

sysmultiobjrefs

2

107

2

16,384

sysmultiobjrefs

2

106

2

16,384

sysnsobjs

1

1

2

16,384

sysnsobjs

1

1

2

16,384

sysnsobjs

2

1

2

16,384

sysnsobjs

2

1

2

16,384

sysobjkeycrypts

1

0

0

0

sysobjkeycrypts

1

0

0

0

sysobjvalues

1

187

5

40,960

sysobjvalues

1

125

3

24,576

sysobjvalues

1

187

25

204,800

sysobjvalues

1

125

25

204,800

sysowners

1

14

2

16,384

sysowners

1

14

2

16,384

sysowners

2

14

2

16,384

sysowners

2

14

2

16,384

sysowners

3

14

2

16,384

sysowners

3

14

2

16,384

sysphfg

1

1

2

16,384

sysphfg

1

1

2

16,384

syspriorities

1

0

0

0

syspriorities

1

0

0

0

syspriorities

2

0

0

0

syspriorities

2

0

0

0

syspriorities

3

0

0

0

syspriorities

3

0

0

0

sysprivs

1

137

2

16,384

sysprivs

1

130

2

16,384

syspru

1

0

0

0

 

 

 

 

 

sysprufiles

1

2

2

16,384

sysprufiles

1

2

2

16,384

sysqnames

1

98

2

16,384

sysqnames

1

97

2

16,384

sysqnames

2

98

2

16,384

sysqnames

2

97

2

16,384

sysremsvcbinds

1

0

0

0

sysremsvcbinds

1

0

0

0

sysremsvcbinds

2

0

0

0

sysremsvcbinds

2

0

0

0

sysremsvcbinds

3

0

0

0

sysremsvcbinds

3

0

0

0

sysrmtlgns

1

0

0

0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

sysrowsetrefs

1

0

0

0

sysrowsetrefs

1

0

0

0

sysrowsets

1

124

2

16,384

sysrowsets

1

91

2

16,384

sysrscols

1

870

17

139,264

sysrscols

1

632

9

73,728

sysrts

1

1

2

16,384

sysrts

1

1

2

16,384

sysrts

2

1

2

16,384

sysrts

2

1

2

16,384

sysrts

3

1

2

16,384

sysrts

3

1

2

16,384

sysscalartypes

1

34

2

16,384

sysscalartypes

1

34

2

16,384

sysscalartypes

2

34

2

16,384

sysscalartypes

2

34

2

16,384

sysscalartypes

3

34

2

16,384

sysscalartypes

3

34

2

16,384

sysschobjs

1

2063

33

270,336

sysschobjs

1

53

2

16,384

sysschobjs

2

2063

33

270,336

sysschobjs

2

53

2

16,384

sysschobjs

3

2063

33

270,336

sysschobjs

3

53

2

16,384

sysschobjs

4

2063

6

49,152

sysschobjs

4

53

2

16,384

sysseobjvalues

1

0

0

0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

syssingleobjrefs

1

155

2

16,384

syssingleobjrefs

1

146

2

16,384

syssingleobjrefs

2

155

2

16,384

syssingleobjrefs

2

146

2

16,384

syssoftobjrefs

1

0

0

0

syssoftobjrefs

1

0

0

0

syssoftobjrefs

2

0

0

0

syssoftobjrefs

2

0

0

0

syssqlguides

1

0

0

0

syssqlguides

1

0

0

0

syssqlguides

2

0

0

0

syssqlguides

2

0

0

0

syssqlguides

3

0

0

0

syssqlguides

3

0

0

0

systypedsubobjs

1

0

0

0

systypedsubobjs

1

0

0

0

systypedsubobjs

2

0

0

0

systypedsubobjs

2

0

0

0

sysusermsgs

1

0

0

0

 

 

 

 

 

syswebmethods

1

0

0

0

 

 

 

 

 

sysxlgns

1

0

0

0

 

 

 

 

 

sysxlgns

2

0

0

0

 

 

 

 

 

sysxlgns

3

0

0

0

 

 

 

 

 

sysxmitbody

1

0

0

0

 

 

 

 

 

sysxmitqueue

1

0

0

0

sysxmitqueue

1

0

0

0

sysxmlcomponent

1

100

2

16,384

sysxmlcomponent

1

99

2

16,384

sysxmlcomponent

2

100

2

16,384

sysxmlcomponent

2

99

2

16,384

sysxmlfacet

1

112

2

16,384

sysxmlfacet

1

112

2

16,384

sysxmlplacement

1

19

2

16,384

sysxmlplacement

1

18

2

16,384

sysxmlplacement

2

19

2

16,384

sysxmlplacement

2

18

2

16,384

sysxprops

1

0

0

0

sysxprops

1

0

0

0

sysxsrvs

1

0

0

0

 

 

 

 

 

sysxsrvs

2

0

0

0

 

 

 

 

 

SUM

2,146,304

SUM

1,245,184

SQL 2008

SQL 2005

object_name

index_id

rows

total_pages

size_bytes

object_name

index_id

rows

total_pages

size_bytes

filestream_tombstone_2073058421

1

0

0

0

 

 

 

 

0

filestream_tombstone_2073058421

2

0

0

0

 

 

 

 

0

 

 

 

 

0

 

 

 

 

0

queue_messages_1977058079

1

0

0

0

queue_messages_1977058079

1

0

0

0

queue_messages_1977058079

2

0

0

0

queue_messages_1977058079

2

0

0

0

queue_messages_2009058193

1

0

0

0

queue_messages_2009058193

1

0

0

0

queue_messages_2009058193

2

0

0

0

queue_messages_2009058193

2

0

0

0

queue_messages_2041058307

1

0

0

0

queue_messages_2041058307

1

0

0

0

queue_messages_2041058307

2

0

0

0

queue_messages_2041058307

2

0

0

0

sysallocunits

1

103

4

32,768

sysallocunits

1

89

2

16,384

sysallocunits

2

103

2

16,384

 

 

 

 

 

sysasymkeys

1

0

0

0

sysasymkeys

1

0

0

0

sysasymkeys

2

0

0

0

sysasymkeys

2

0

0

0

sysasymkeys

3

0

0

0

sysasymkeys

3

0

0

0

sysaudacts

1

0

0

0

 

 

 

 

0

sysbinobjs

1

23

2

16,384

sysbinobjs

1

23

2

16,384

sysbinobjs

2

23

2

16,384

sysbinobjs

2

23

2

16,384

sysbinsubobjs

1

3

2

16,384

sysbinsubobjs

1

0

0

0

sysbinsubobjs

2

3

2

16,384

sysbinsubobjs

2

0

0

0

 

 

 

 

0

 

 

 

 

0

syscerts

1

0

0

0

syscerts

1

0

0

0

syscerts

2

0

0

0

syscerts

2

0

0

0

syscerts

3

0

0

0

syscerts

3

0

0

0

syscerts

4

0

0

0

syscerts

4

0

0

0

 

 

 

 

0

 

 

 

 

0

 

 

 

 

0